MySQL

tips and tricks for using MySQL

Search and Replace

I frequently get stuck cleaning up the unicode characters when importing data from older versions of MySQL. This snippet is the one I use most often for the replacement of garbage characters to the Swedish äöå.

update TABLE_NAME set FIELD_NAME =
replace(FIELD_NAME,'find this string','replace it with this one');



UPDATE `node_revisions` SET teaser=replace(teaser,'ä','ä');
UPDATE `node_revisions` SET teaser=replace(teaser,'ö','ö');
UPDATE `node_revisions` SET teaser=replace(teaser,'Ã¥','å');
UPDATE `node_revisions` SET body=replace(body,'ä','ä');
UPDATE `node_revisions` SET body=replace(body,'ö','ö');
UPDATE `node_revisions` SET body=replace(body,'Ã¥','å');
UPDATE `node_revisions` SET title=replace(title,'ä','ä');
UPDATE `node_revisions` SET title=replace(title,'ö','ö');
UPDATE `node_revisions` SET title=replace(title,'Ã¥','å');