Jump to content

Archived

This topic is now archived and is closed to further replies.

john_6767

using mysql replace() to replace quotes??

Recommended Posts

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?

Share this post


Link to post
Share on other sites
You just have to make sure you escape these characters.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.