0

Is there any way to perform an update for normalizing a field using a single query?

Example:

UPDATE person SET name = REPLACE(name, 'á', 'a');
UPDATE person SET name = REPLACE(name, 'é', 'e');
UPDATE person SET name = REPLACE(name, 'í', 'i');
UPDATE person SET name = REPLACE(name, 'ó', 'o');
UPDATE person SET name = REPLACE(name, 'ú', 'u');
pilcrow
  • 56,591
  • 13
  • 94
  • 135
rfc1484
  • 9,441
  • 16
  • 72
  • 123
  • Maybe you could write a multi-replace function in sql. – biziclop Mar 22 '12 at 15:24
  • 1
    You may want to look into user defined regex replace functions in mysql. http://stackoverflow.com/questions/1755408/mysql-regex-replace – Gohn67 Mar 22 '12 at 15:29

2 Answers2

1

You can chain the replace calls, so it can be done in a single query:

UPDATE person SET name = REPLACE(REPLACE(REPLACE(name, 'á', 'a'), 'é', 'e'), 'í', 'i')

But this quickly becomes an unmaintainable mess. If you're simply trying to replace accented characters with their unaccented equivalents, maybe a character set change would be of more use.

Marc B
  • 356,200
  • 43
  • 426
  • 500
0

Replace like this you all are talking about become mess if u want to replace all alphabets. You can make one table with two colums such as 1st column consist 'á' and second 'a' similarly for whole. and then make a procedure to fetch each row of new table and then done the replace.