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.

Answer 1

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.