JVerstry JVerstry - 1 year ago 45
MySQL Question

How to update badly encoded characters in MySql table?

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

The following query:

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

returns two lines describing the issue:

enter image description here

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 suggested possible duplicate does not address the issue I am raising.

Answer Source

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.