How to replace Æ to AE for all strings in my table?

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?

Can you elaborate what this is for? Is it to work around an encoding problem? Because those can almost always be fixed — Pekka, Sep 01 '13 at 19:31
When I try to retrieve a file with the exact name, it tries to retrieve the following: Ã�therling. And it seems it would be best to rename the Æ to AE which mitigates the fundamental problem in the first place. — Howard, Sep 01 '13 at 19:47
You should fix your File IO code, instead of breaking your data. — Niels B., Sep 01 '13 at 19:53
I just added a separate column for the filename which has all Æ to AE. Didn't have to touch the data. — Howard, Sep 01 '13 at 19:58
You'll have the same problem next time another special character comes across - `Ü` or `é` or `字` or whatever. You should really either fix the encoding issue, or use a universal method like [PHP: Replace umlauts with closest 7-bit ASCII equivalent in an UTF-8 string](http://stackoverflow.com/q/158241) — Pekka, Sep 01 '13 at 20:07

score 1 · Answer 1 · answered Sep 01 '13 at 19:35

1

You could always dump (export) the table contents, getting a create and insert command that you paste into a text editor, replace all Æ-s to AE, drop the table and run the exported script that wil re-create it with the changes you made.

answered Sep 01 '13 at 19:35

n_ivkovic021

33
5

score 1 · Accepted Answer · edited May 23 '17 at 10:25

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.

How to replace Æ to AE for all strings in my table?

2 Answers2