JVerstry JVerstry - 5 months ago 10
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:

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:

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

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.

Comments