Jump to content


Photo

using mysql replace() to replace quotes??


  • Please log in to reply
4 replies to this topic

#1 john_6767

john_6767
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 28 August 2006 - 02:59 AM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 August 2006 - 10:20 PM

You just have to make sure you escape these characters.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 john_6767

john_6767
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 30 August 2006 - 10:10 PM

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?

#4 T.Stedel

T.Stedel
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 30 August 2006 - 10:19 PM

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?



#5 john_6767

john_6767
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts

Posted 31 August 2006 - 12:20 AM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users