How to remove accents in MySQL?

Question

I've just compiled a database of 1 million place names. I'm going to use it in an auto-complete widget to look up cities. A lot of these places have accents... I want to be able to find records when a user types the name without an accent.

In order to do this, I've got a 2nd column with an unaccented copy of the name. Many of these records are still blank, so I want to write a query to fill them in. Is this possible in straight MySQL? If so, how?

Answer 1

If you set an appropriate collation for the column then the value within the field will compare equal to its unaccented equivalent naturally.

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; Query OK, 0 rows affected (0.00 sec)  mysql> SELECT 'é' = 'e'; +------------+ | 'é' = 'e' | +------------+ |          1 | +------------+ 1 row in set (0.05 sec) 

Answer 2

I had the same problem, so I wrote a list of querys based on a PHP script I have to remove accents and make SEO friendly URLs:

Maybe you would like to add other special characters, such as the $ or £ symbols...

UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Š','S'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'š','s'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ð','Dj'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ž','Z'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ž','z'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'À','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Á','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Â','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ã','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ä','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Å','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Æ','A'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ç','C'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'È','E'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'É','E'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ê','E'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ë','E'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ì','I'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Í','I'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Î','I'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ï','I'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ñ','N'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ò','O'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ó','O'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ô','O'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Õ','O'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ö','O'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ø','O'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ù','U'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ú','U'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Û','U'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ü','U'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ý','Y'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Þ','B'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ß','Ss'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'à','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'á','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'â','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ã','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ä','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'å','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'æ','a'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ç','c'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'è','e'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'é','e'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ê','e'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ë','e'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ì','i'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'í','i'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'î','i'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ï','i'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ð','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ñ','n'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ò','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ó','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ô','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'õ','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ö','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ø','o'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ù','u'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ú','u'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'û','u'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ý','y'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ý','y'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'þ','b'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ÿ','y'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ƒ','f'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'.',' '); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,' ','-'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'--','-');  UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ě','e'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ž','z'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'š','s'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'č','c'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ř','r'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ď','d'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ť','t'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ň','n'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'ů','u');  UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ě','E'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ž','Z'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Š','S'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Č','C'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ř','R'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ď','D'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ť','T'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ň','N'); UPDATE TABLE_NAME SET COLUMN = REPLACE(COLUMN,'Ů','U');  UPDATE TABLE_NAME SET COLUMN = LOWER(COLUMN);