Jump to content


using mysql replace() to replace quotes??

  • Please log in to reply
4 replies to this topic

#1 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??


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

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

  • 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

  • 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


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

  • 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