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!

Advertisements

Comments Off on Clean up unwanted characters in a Mysql Database

%d bloggers like this: