如何将Æ替换为表中所有字符串的AE？

Question

I have names in my database like Ætherling or other words where the AE can be in the middle of a string. I'd like to change all instances of Æ to AE. All strings in my database are utf8_unicode.

This is what I currently have:

UPDATE `cards` set name = REPLACE(name,'Æ','AE');

However this only does it for the particular column. How can I do the entire table in MySQL?

Answer 1

I don't think it have much sense to run query that will update ALL columns, as some of them might have not contain varchar values.

You must specify your fields explicitly:

UPDATE `cards` set 
   name = REPLACE(name,'Æ','AE'), 
   other = REPLACE(other,'Æ','AE'), 
   andother = REPLACE(andother,'Æ','AE');

Or you can make use of query from here: Replace all fields in MySQL

select concat(
       'UPDATE my_table SET ',
       column_name,
       ' = REPLACE(', column_name, ', ''a'', ''e'');')
from information_schema.columns
where table_name = 'my_table';

It will generate a set of update queries. You copy them, paste them and run them.