0

We have table which consists of two different encoding data (utf8 and latin1) which has been inserted from two different use cases of application. We are getting broken strings issue for other languages text if we get the data in one encoding.We need to convert total table data into single encoding.

Eg: Table X id name data encoded 1 ébarber - utf8 2 à gogo - latin1

if we use "latin1" connection charset, we get issue with "ébarber"(broken strings). if we use "utf8" connection charset, we get issue with "à gogo"(broken strings).

How can we convert this table data into single encoding either utf8 or latin1? Please share your thoughts to fix this issue.

  • 1
    What DBMS are you using? Oracle, SQL Server, Postgres, DB2, SQLite, soemthing else? – ypercubeᵀᴹ Jan 28 '16 at 11:33
  • 1
    @ypercubeᵀᴹ definitely not SQL Server if they are storing data in the table as UTF-8 ;-) – Solomon Rutzky Jan 28 '16 at 15:54
2

Depending on what RDBMS you are using:

  • You can try altering the Latin1-encoded column to use an encoding of UTF-8, if the RDBMS supports such an operation.

    If you are using MySQL, you should look at this related Question on S.O.:
    Converting mysql tables from latin1 to utf8

  • You can try:

    • Adding a new column with an encoding of UTF-8
    • UPDATE new_column = Latin1_column (I am assuming that the RDBMS would be able to implicitly convert since it knows both encodings)
    • DROP Latin1_column
    • rename new_column to be former Latin1_column's name
1

This is a rather general question so my answer will be also quite general...

Consolidate everything to UTF-8!

If possible reload at least the latin1 entries: This time converting latin1 to UTF-8 which should always be possible.

Depending on the database product the conversion can be performed by the import tool. Starting with flat files and a custom import procedure one can also use the recode tool.

It begs the question how the column containing the char data is defined. Depending on the definition and the database even the UTF-8 data might already be corrupted on import.

Not the answer you're looking for? Browse other questions tagged or ask your own question.