0

Originally, we ran a database with UTF-8 encoding and we had to migrate to a different server which was using latin-1 by mistake. The problem is that most names contain special foreign characters and they get rendered weird without the proper encoding:

For example:

Nezihe Şükran Akkaş
LÜTFİ ÇOBAN
Eren Karagözlü

I was able to convert it back to UTF using the following query:
SELECT convert(cast(convert(name using latin1) as binary) using UTF8) AS name FROM users;

The above names now appeared correctly:

Nezihe Şükran Akkaş
LÜTFİ ÇOBAN
Eren Karagözlü

However, all the data that was previously encoded as proper UTF-8 now appears as (NULL)

My question is how do I convert only the broken encoding rows and leave the properly encoded one's untouched? Right now, it's "either or". The problem is they are mixed in terms of order so I can't separate them by ID.

Any clue would help. Thanks!

enter image description here

Miro
  • 8,402
  • 3
  • 34
  • 72
  • i noticed this some weeks ago, but didn't report it – nbk Aug 16 '20 at 23:19
  • Fixes _depend on the situation_: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases More discussion: https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Aug 17 '20 at 06:04

1 Answers1

1

Can you not just select coalesce( convert(...), name )?

ysth
  • 96,171
  • 6
  • 121
  • 214