If you ever find the need to do a bulk search and replace in a text field of a database table you can do it without the need for external scripts thanks to a small bit of SQL.

Just moved from MySQL 4.0 to 4.1 and seeing wierd characters everywhere? Well I had this ‘privelage’ enforced by a host without any notice, so it was bloody important to get this up and running.

Here’s how to fix it

Firstly dump your existing MySQL 4.0 database:

mysqldump –-user=root -p –-default-character-set=latin1
–-skip-set-charset database_name > database_name.sql

Run this regular expression against the sql dump and output to new file:

sed -r ’s/latin1/utf8/g’ database_name.sql > database_name.utf.sql