How to update badly encoded characters in MySql table?

Question

In an existing database, we have discovered some text entries where characters with accents were badly encoded.

The following query:

SELECT 
    PR.Product_Ref__ AS ProductCode,
    CONCAT(P.Name, IF (PR.Misc <> '', CONCAT(' ', PR.Misc), '')) AS Name
FROM
    Product AS P
        INNER JOIN
    Product_Ref AS PR ON P.Product__ = PR.Product__
WHERE
    Name like "%é%" AND
    PR.Product_Ref__ IN ( 659491, 657274 )

returns two lines describing the issue:

Sometimes, e with accent has been inserted properly, sometimes not.

How can I detect and update such issue with an UPDATE ... WHERE ...? Or should I use a different statement/method?

P.S.: The corresponding application is developed in PHP.

P.S.2: The answers in the suggested possible duplicate question do not address the issue I am raising. It is not related to the collation of the database.

Possible duplicate of [how to deal with accents and strange characters in a database?](http://stackoverflow.com/questions/33219970/how-to-deal-with-accents-and-strange-characters-in-a-database) — David Ferenczy Rogožan, Jun 23 '16 at 09:41
You should solve the problem with the inserts first, see for example http://stackoverflow.com/questions/279170/utf-8-all-the-way-through — jeroen, Jun 23 '16 at 09:45

miguelbgouveia · Accepted Answer · 2016-06-23T09:49:16.360

You just can simple detect all the occurrences that you want to correct and the use a simple update clause to make the substitutions.

Example for the case that you describe:

UPDATE Product
SET Name = REPLACE(Name, 'Ã©', 'é')
WHERE Name like '%Ã©%'

You can run this updates directly in mysql databases, using the command line or in a specific mysql user interface client application. Or if you like, using php functions that run sql statements in the database.

How to update badly encoded characters in MySql table?

1 Answers1