I have already seen similar problems on the web, but none answers what I want to do. I have a database in MySQL, which is now fully encoded in UTF8. Some of the values of the columns are encoded in UTF8 but contain latin1 characters. I know which ones, so I want to do a global replace.
I looked CAST() and CONVERT() for MySQL but whatever combinations I use, I cannot seem to get the result I want.
The example: my column noclient contains the value Álvarez (accent on the A). The charset of the column, the table and the database is UTF8. So when I query the column, I get ?lvarez. However, I have a parallel PHP system running which is still on latin1. When that queries the column, it displays Álvarez correctly.
What I need to do is read the value as latin1 and then convert it to utf8. From the SQL command, when I do the select, I get ?lvarez. I assume this is because the SQL command line is running in UTF8.
Pseudo-code-wise, I imagine the solution to be
CONVERT(CAST(noclient, latin1) to UTF8) but that doesn't seem to be working for me.
I expected this to be the solution, but it doesn't work. What am I missing:
select CONVERT(CAST(noclient AS CHAR CHARACTER SET latin1) USING utf8) as name
