January 2nd, 2013, 04:27 AM
Change LATIN1 data to UTF8 data in UTF-8 datbase
I have database with Unicode characterset (ENCODING = 'UTF8').
1. Till today data has been entered to tables in encoding "ISO-8859-1" using Java application.
That means that string "äš" was saved down as bytes/codes used in characterset "ISO-8859-1". The letter "ä" is saved down with code 228 in database, if i examine the value with function "ascii", and letter "š" is saved down with code 154, those codes are saved down:
2. Now i changed Java application to use UTF-8 and entered with encoding "UTF-8" new data to tables.
That means that string "äš" is saved down as bytes/codes used in characterset "UTF-8". The letter "ä" is saved down with code 228 in database (same code as in LATIN1 case), if i examine the value with function "ascii", and letter "š" is saved down with code 353 (DIFFERENT code from LATIN1 case), those codes are saved down:
As you see in step 1 i saved to database LATIN1 encoded data and with step 2 i started to save down UTF-8 encoded data.
As you see LATIN1 encoded data is mostly the same as UTF-8 encoded data, for example character "ä" is saved down in both encodings as code/byte "228", but only minor special characters like "š" differ.
The character "š" in LATIN1 case was saved to database as code 154, but UTF-8 case saves down code 353 instead.
I need all existing data to be converted to UTF-8. That means that data entered in step 1 with LATIN1 encoding needs to be converted to UTF-8. The LATIN1 "š" (154) should be replaced/updated to UTF-8 "š" (353).
How can i make such conversion?
Note that database encoding stays UTF-8. Only i need now to convert existing data somehow from LATIN1 to UTF-8, maybe with UPDATE clause.
January 2nd, 2013, 05:48 AM
I think i can try to define question shorter.
In UTF-8 database i inserted a string that has LATIN1 encoding. Now i want to change the string encoding to UTF-8. How can i do that? I don't need to change database encoding, the string that was inserted need to be changed there.
January 3rd, 2013, 03:22 AM
I got solution, idea is in below query. Topic can be closed.