using mysql replace() to replace quotes??

[john_6767]

ok, so i am tidying a databse up and need to replace all the " with Inch and all the ' with foot (in the data..)

the following sql code doesn't work when using quotes and doubles quotes, it must think they're syntax??

update TABLE set COLUMN = replace(COLUMN, OLD_VALUE, NEW_VALUE);

any ideas, i'm new to sql, maybe theres something i can put around the " saying that they are a string?

[fenway]

You just have to make sure you escape these characters.

[john_6767]

does escape mean to make sure that they are not entered into the db as data? because i already have a database that if full of data, is there some way to add slashes though sql in the database so i don't have to go through the rows one by one?

[T.Stedel]

Can you elaborate more? If your question is as simple as I think, then knowing that \ will force anything directly after it to be interpretted literally in a database will probably help you out (Except for predefined escape sequences).

INSERT INTO table VALUES('This is a \" test');
Inserts: This is a " test

Edit:

Looking at your post a little bit, I might understand your problem better.

UPDATE table SET column = replace(column, '\"', 'feet');
Does that sound plausible?

[john_6767]

that sounds like what i thought i was after, although after more thought and looking at your first comment i may not need to update the database.

It seems like better practise to leave the database as standard data and then add/remove as needed the slashes by using php code.

Thanks for the tip though in putting the backslash before the " in the replace function.