mysql command

[honging]

is there a mysql command that will change a certain number of characters to something else?

the problem is someone scripted a lyrics script, except it takes each quote and makes it multiple quotes. i don't even know ow to fix it, but i want to at least update the database so it doesn't do '' instead of '

how would i go about changing all the '' to ' ?

thanks!

[martm]

i assume this was done to counter the problem of inserting data that contains single quotes into a database.

see http://www.4guysfromrolla.com/webtech/051899-1.shtml for an example using asp.

one option would be to just convert double quotes back to single quotes after retrieving the info from the db.

Alternativley this might be useful for you.

the string replace function:
http://www.mysql.com/doc/S/t/String_functions.html

you should be able to use it like this:
UPDATE table SET field = REPLACE(field, '\"', '\'');

this will replace *all* instances of " with ' for that field.

best check it on a test database first though

[honging]

there seemed to be an error with that... i couldn't get it to work...

are the three apostrophes at the end supposed to be there?

[martm]

sorry, that should have been.
UPDATE table SET field = REPLACE(field, '\"', '\'');

or in case this post messes up again

UPDATE table SET field = REPLACE(field, '\(double quotes)', '\(single quotes)');

you would replace (double quotes) with " and (single quotes) with '


those darn escaping chars again

hope this is clear and hope it works this time

[honging]

argh, when i tried to do

update lyrics set lyrics = replace(lyrics,'\(double apostrophes)','\(single quote)');

it gave me an error saying that \(double quotes)' is not a known command... SO i tried changing all the single quotes to semicolons then tried changing two semicolons (since '' would become ; back into single quotes but now they've simply dissappeard from my database all together

[Paul S]

Originally posted by honging
argh, when i tried to do

update lyrics set lyrics = replace(lyrics,'\(double apostrophes)','\(single quote)');

it gave me an error saying that \(double quotes)' is not a known command... SO i tried changing all the single quotes to semicolons then tried changing two semicolons (since '' would become ; back into single quotes but now they've simply dissappeard from my database all together

update lyrics set lyrics = replace(lyrics,'"',"'")

the first one : single quote + double quote + single quote,
the second one : double quote + single quote + double quote.

That way you don't need to escape the quotes.
Now in php that should be written as

$sql = 'update lyrics set lyrics = replace(lyrics,\\'"\\',"\\'")';

Paul

[martm]

sorry, i obviously wasn't clear enough in my second post and confused the matter

i only used the (double quotes) (single quotes) stuff as one of the \ chars disappeared from my first post.

looks like Paul has sorted it out tho' with his clearer answer