9

I have a database that is uft8_general_ci, only problem is up until this point an application coded by a previous developer seems to have been working with the database in latin-1.

I have since changed how the app uses the database and can now store an umlaut as an umlaut instead of ü. The problem now is that the application reads the previously existing data from the database as (example) 'Süddeutsche' instead of 'Süddeutsche'.

Is there anyway to convert the data inside the database from one format to the other?

Regards

Edit:

ALTER TABLE TableName MODIFY ColumnName ColumnType CHARACTER SET latin1;
ALTER TABLE TableName MODIFY ColumnName ColumnType CHARACTER SET binary;
ALTER TABLE TableName MODIFY ColumnName ColumnType CHARACTER SET utf8;

This worked for me.

mr12086
  • 1,137
  • 13
  • 29
  • possible duplicate of [How to correct double-encoded UTF-8 strings sitting in MySQL utf8_general_ci fields?](http://stackoverflow.com/questions/5951871/how-to-correct-double-encoded-utf-8-strings-sitting-in-mysql-utf8-general-ci-fie) – Álvaro González Jan 16 '12 at 12:22
  • Thank you, not quite the same but the answer works! – mr12086 Jan 16 '12 at 13:38
  • After some testing if I run this 2x on some of my columns the data will break - all data after a particular character/point are lost. Is this simply because I ran the command 2x, or is there a change its breaking lots of my data all over the database? – mr12086 Feb 17 '12 at 12:37

3 Answers3

2

You could try SET NAMES to let the Database talk in latin-1 with your application while storing in utf-8 or you will need to convert all previous Datasets to utf-8-Strings

bardiir
  • 14,556
  • 9
  • 41
  • 66
1

try

ALTER DATABASE your_db DEFAULT CHARACTER SET = 'utf8' COLLATE 'utf8_unicode_ci';

and

ALTER TABLE a CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

ALTER TABLE b CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';
ALTER TABLE c CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

don't forget to replace the 'ß':

UPDATE a SET field_1 = REPLACE(field_1, 'ß', 'ss') WHERE label LIKE '%ß%';
silly
  • 7,789
  • 2
  • 24
  • 37
1

http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/

Using what is listed here has fixed all my problems. I used this with my live data and have had no issues!

mr12086
  • 1,137
  • 13
  • 29