MySQL Forums
Forum List  »  Newbie

replacing (escaping) unescaped apostrophes already in mysql
Posted by: Jeff Johnson
Date: January 20, 2010 07:34PM

I am trying to replace apostrophes in my mysql database for cities with apostrophes such as O'Briensville, etc. The data was imported from another database with bigdump and none of the apostrophes are escaped. Since I need to be able to match these city names with a mysql_real_escape_string search string (O\'Briensville => O\'Briensville), the apostrophes in the database have to be escaped (I think?)..
So this is what I tried.
this works: SELECT * FROM cities WHERE city LIKE '%\'%'
returns all cities with an apostrophe.
however, this does not work:
UPDATE cities SET city=replace(city,'\'','\\\'') WHERE city LIKE '%\'%'

any ideas before I don't have any more hair left? =)

Options: ReplyQuote

Written By
replacing (escaping) unescaped apostrophes already in mysql
January 20, 2010 07:34PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.