Clean up Character Set mess in the same field of one table
Posted by: Tobias Keller
Date: April 07, 2014 09:42AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Clean up Character Set mess in the same field of one table
2746
April 07, 2014 09:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.