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.