Wwwebber's Blog

Clean up unwanted characters in a Mysql Database

Posted in Uncategorized by wwwebber on July 25, 2009

I recently had to cleanse a large MySql Database–over 130,000 records and many of the records had one or both of the following problems

1) quotes were not escaped so you’d see a varchar with unescaped single quotes scattered throughout the string–that’s a problem since a single quote indicates the end of the varchar to MySql!

2) The PHP side of the app did not work well ( in fact at all) with periods or commas…and of course we had a lot of them throughout the DB

So here’s the SQL that might help you

the SQL to remove all periods from someTable’s column called someColumn

UPDATE  `someTable` SET  `someColumn`= replace( `someColumn` , ‘.’, ” )

A little trickier looking was escaping all of the single quotes thorughout the db. Here’s how you add a single escape slash before the single quotes:

UPDATE  `someTable` SET  `SsomeColumn`= replace( `someColumn` , ‘\”, ‘\\\” )

I know that last bolded part probably looks wierd–just remember that you have to escape the escape character as well as the quote!


Comments Off on Clean up unwanted characters in a Mysql Database

%d bloggers like this: