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 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

--
_ _ _ _
(_|| | |(_|><
_|
http://gmax.oltrelinux.com

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.