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)
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.
精彩评论