How to remove accents in MySQL?

mysql utf-8 diacritics
mpen picture mpen · Jan 27, 2011 · Viewed 54k times · Source

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

Ignacio Vazquez-Abrams picture Ignacio Vazquez-Abrams · Jan 27, 2011

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)

Related questions

normalizing accented characters in MySQL queries

I'd like to be able to do queries that normalize accented characters, so that for example: é, è, and ê are all treated as 'e', in queries using '=' and 'like'. I have a row with username field set to 'rené', …

Read More
Convert characters to UTF8 in MySQL (é to é)

I've got a MySQL problem. My whole website except my database is in UTF8. I just recently tried to convert it to UTF8 with all kind of commands, but the main issue is still there: the accents don't work. Instead …

Read More
What's the difference between utf8_general_ci and utf8_unicode_ci?

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

Read More