Clean up Character Set mess in the same field of one table
Dear MySQL experts,
I have a problem with a table that contains strings using different character sets (latin1 and utf8) in the same field.
The cause was probably a switch of the field's collation at some point and/or a change in the application that saved the strings into the field.
I would like to change the strings in to one common character set.
At the moment I can get the correct values for the two types of entries as follows:
For "group 1":
SET NAMES utf8;
SELECT id, groupname, stringfield FROM tablename;
=> 1, "group 1", "text with correct ä,ö,ü, ..."
=> 2, "group 2", "text with incorrect ä, ö ..."
------------------
For "group 1":
SET NAMES latin1;
SELECT id, groupname, stringfield FROM tablename;
=> 1, "group 1", "text with correct ä,ö,ü,..."
=> 2, "group 2", "text with incorrect � � �"
I have tried different things, like using CONVERT(stringfield USING utf8/latin1), CAST(stringfield AS CHAR(2000) CHARACTER SET utf8/latin1 but to no avail.
I would like to convert either the utf8 strings into latin1 or vice versa and save the compatible strings in a new field.
Any ideas on how to do that?
Thank you very much in advance for any help!
Tobias