CODEWITHSUNDEEP
phphtmlbrowsercharacter-encodingspecial-characters
siliconpi
siliconpi

Reputation: 8297

Search and replace in MySQL database?

I have an unusual problem (this is linked to Browser displays � instead of ´)

I had mismatched character encoding settings on my server (UTF-8) and application (ISO-8859-1), so a third person tasked with entering Spanish translations, entered the words properly at his end, but they weren't saved correctly in the database.

I have subsequently fixed the problem and the server is now ISO-8859-1 as well. [I set 

default_charset = "iso-8859-1"

in php.ini]

I do see a pattern in what is in the system, for example the following appears on the system:

Nombre de la organización*

This needs to be:

Nombre de la organización*

ie, I need to search and replace 'ó' with 'ó'.

How can I do so for an entire table (all fields)? (there will be other such corrections as well)

Upvotes: 1

Views: 767

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76892

Use the replace function. Simple example:

SELECT REPLACE('www.mysql.com', 'w', 'Ww');

Result: 'WwWwWw.mysql.com'

Now, if you have a table called Foo and you want to replace those characters in a field called bar, you can do the following:

update Foo set bar = Replace(bar, 'ó', 'ó');

Do this for all the affected fields and the problem is solved.

Best regards, Lajos Arpad.

Upvotes: 2

Related Questions