1

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)

Community
  • 1
  • 1
siliconpi
  • 8,105
  • 18
  • 69
  • 107
  • 2
    You'd be best off setting everything to UTF-8, rather than ISO-8859-1. – JAL Nov 13 '10 at 02:29
  • @Alex JL: Good advice. However, the question refers to finding and replacing some text from all the rows of a field (or more fields) in a table. – Lajos Arpad Nov 13 '10 at 02:37
  • @matt: I'm puzzled about that "all fields". You probably have a primary key, so you don't need this replace to happen at all fields. Maybe you've meant all rows/records? (If you meant that, then my answer contains the solution) – Lajos Arpad Nov 13 '10 at 02:42
  • @matt74tm because it will handle all the character from IS-8859-1, and then some. – JAL Nov 13 '10 at 03:09
  • @Lajos Arpad right, that's why it was a comment and not an answer! – JAL Nov 13 '10 at 03:10

1 Answers1

2

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.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175