Professional Web Applications Themes

Replace string query - double trouble! - MySQL

UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,"stringOne","stringTwo") I use the above code to strip strings from my table, and most of the time this works well. However, I have several fields with double quotes within, and I would like to remove all double quotes. Doing this does not work, and generates an error: UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,""","") Can anyone point me in the right direction? Regards, Gary....

  1. #1

    Default Replace string query - double trouble!

    UPDATE `tableName` SET `fieldName` =
    REPLACE(fieldName,"stringOne","stringTwo")

    I use the above code to strip strings from my table, and most of the time
    this works well. However, I have several fields with double quotes within,
    and I would like to remove all double quotes.

    Doing this does not work, and generates an error:

    UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,""","")

    Can anyone point me in the right direction?

    Regards,

    Gary.


    Gary@garywhittle.co.uk Guest

  2. #2

    Default Re: Replace string query - double trouble!

    [email]Garygarywhittle.co.uk[/email] wrote:
    > UPDATE `tableName` SET `fieldName` =
    > REPLACE(fieldName,"stringOne","stringTwo")
    >
    > I use the above code to strip strings from my table, and most of the time
    > this works well. However, I have several fields with double quotes within,
    > and I would like to remove all double quotes.
    >
    > Doing this does not work, and generates an error:
    >
    > UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,""","")
    >
    > Can anyone point me in the right direction?
    >
    > Regards,
    >
    > Gary.
    >
    >
    You need one more double quote to escape it:
    UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,"""","")

    or you can use single quotes to quote a double quote:
    UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,'"',"")

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Replace string query - double trouble!

    Giuseppe Maxia wrote:

    [replace double quotes in strings]
    >
    > You need one more double quote to escape it:
    > UPDATE `tableName` SET `fieldName` = REPLACE(fieldName,"""","")
    Additionaly it is worth noting, that using double quotes as string
    delimiter is an extension of MySQL. The SQL standard uses double quotes
    for identifiers. MySQL can be configured in both ways, thus using double
    quotes can cause one or the other behavior.

    The best practice should be to always use single quotes for strings.

    UPDATE `tableName` SET `fieldName` = REPLACE(`fieldName`,'"','')

    Greetings
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

Similar Threads

  1. CF double quoting my single quotes in query string
    By lerxst3 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 7th, 12:37 AM
  2. Trouble using Replace() function in CFN file
    By jay54321 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: June 7th, 10:45 AM
  3. replace() string
    By dweeres in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: May 1st, 04:26 AM
  4. Replace value in a string
    By Eric in forum PHP Development
    Replies: 5
    Last Post: October 29th, 02:36 PM
  5. Replies: 4
    Last Post: September 12th, 04:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139