Replacing characters in MySQL table data

by Martin Westin in


This may be useful when for example the character encoding has been messed up and you need to correct existing data. This example will correct swedish accented characters in a uft8 - latin1 mixup.

UPDATE my_table
set my_field = replace(my_field,'Ã¥','å');

UPDATE my_table
set my_field = replace(my_field,'ä','ä');

UPDATE my_table
set my_field = replace(my_field,'ö','ö');

UPDATE my_table
set my_field = replace(my_field,'Ã…','Å');

UPDATE my_table
set my_field = replace(my_field,'Ä','Ä');

UPDATE my_table
set my_field = replace(my_field,'Ö','Ö');

(Yes, you can concatenate them if you like but they become very hard to read.)