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?

look on this post http://stackoverflow.com/questions/2302813/normalizing-accented-characters-in-mysql-queries, you dont need to make another column, change the query — Haim Evgi, Jan 27 '11 at 07:10
@eric, because that would get huge. PHP's iconv() would help. I will note that within the TCPDF source, though, there's many special consideration handled that aren't native to whatever library PHP is using (in order to be very Unicode safe/aware). — zanlok, Jan 27 '11 at 07:14
@Eric Fortis: Given all the possible accent combinations, this is going to be a *long* regex, and it's likely that you'll forget some. Also, some characters don't convert quite logically into ASCII in some encodings (e.g. in German, `ö` could become `oe`) — Piskvor left the building, Jan 27 '11 at 07:16
@Haim: Did you read the first and only answer? It suggests adding another column. — mpen, Jan 27 '11 at 07:37

score 31 · Accepted Answer · answered Jan 27 '11 at 07:09

31

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)

answered Jan 27 '11 at 07:09

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

question.. how well would this work for very non-English characters? – zanlok Jan 27 '11 at 07:15
1

@zanlok: Not so well. It does a few, such as "ß" being equal to "ss", but much further and it breaks down. – Ignacio Vazquez-Abrams Jan 27 '11 at 07:21
`"ß" = "ss" COLLATE utf8_unicode_ci` -- is a match. "Combining diacriticals also work in that collation. – Rick James Jun 11 '15 at 01:25
`ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_520_ci` – Lee Goddard Sep 09 '20 at 17:55

score 26 · Answer 2 · edited Feb 20 '17 at 14:28

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);

A) This actually answers the title question B) extremly useful when you have a large list of items with no slug/pretty-url pre-generated and you want to generate them directly in DB (because of the large amount of values) BTW: I would add `ě, ž, š, č , ř , ď, ť, ň, ů` — jave.web, Feb 20 '17 at 14:20
It *does* remove the accents, however, some languages use different representations for some values (like in german 'ä' is 'ae', 'ö' is 'oe', ...). — Gerrit-K, Feb 02 '18 at 15:36

score 16 · Answer 3 · edited Aug 21 '19 at 08:05

I share this maybe can help....:

DELIMITER //
CREATE OR REPLACE FUNCTION `remove_accents`(`str` TEXT)
    RETURNS text
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT ''

BEGIN

    SET str = REPLACE(str,'Š','S');
    SET str = REPLACE(str,'š','s');
    SET str = REPLACE(str,'Ð','Dj');
    SET str = REPLACE(str,'Ž','Z');
    SET str = REPLACE(str,'ž','z');
    SET str = REPLACE(str,'À','A');
    SET str = REPLACE(str,'Á','A');
    SET str = REPLACE(str,'Â','A');
    SET str = REPLACE(str,'Ã','A');
    SET str = REPLACE(str,'Ä','A');
    SET str = REPLACE(str,'Å','A');
    SET str = REPLACE(str,'Æ','A');
    SET str = REPLACE(str,'Ç','C');
    SET str = REPLACE(str,'È','E');
    SET str = REPLACE(str,'É','E');
    SET str = REPLACE(str,'Ê','E');
    SET str = REPLACE(str,'Ë','E');
    SET str = REPLACE(str,'Ì','I');
    SET str = REPLACE(str,'Í','I');
    SET str = REPLACE(str,'Î','I');
    SET str = REPLACE(str,'Ï','I');
    SET str = REPLACE(str,'Ñ','N');
    SET str = REPLACE(str,'Ò','O');
    SET str = REPLACE(str,'Ó','O');
    SET str = REPLACE(str,'Ô','O');
    SET str = REPLACE(str,'Õ','O');
    SET str = REPLACE(str,'Ö','O');
    SET str = REPLACE(str,'Ø','O');
    SET str = REPLACE(str,'Ù','U');
    SET str = REPLACE(str,'Ú','U');
    SET str = REPLACE(str,'Û','U');
    SET str = REPLACE(str,'Ü','U');
    SET str = REPLACE(str,'Ý','Y');
    SET str = REPLACE(str,'Þ','B');
    SET str = REPLACE(str,'ß','Ss');
    SET str = REPLACE(str,'à','a');
    SET str = REPLACE(str,'á','a');
    SET str = REPLACE(str,'â','a');
    SET str = REPLACE(str,'ã','a');
    SET str = REPLACE(str,'ä','a');
    SET str = REPLACE(str,'å','a');
    SET str = REPLACE(str,'æ','a');
    SET str = REPLACE(str,'ç','c');
    SET str = REPLACE(str,'è','e');
    SET str = REPLACE(str,'é','e');
    SET str = REPLACE(str,'ê','e');
    SET str = REPLACE(str,'ë','e');
    SET str = REPLACE(str,'ì','i');
    SET str = REPLACE(str,'í','i');
    SET str = REPLACE(str,'î','i');
    SET str = REPLACE(str,'ï','i');
    SET str = REPLACE(str,'ð','o');
    SET str = REPLACE(str,'ñ','n');
    SET str = REPLACE(str,'ò','o');
    SET str = REPLACE(str,'ó','o');
    SET str = REPLACE(str,'ô','o');
    SET str = REPLACE(str,'õ','o');
    SET str = REPLACE(str,'ö','o');
    SET str = REPLACE(str,'ø','o');
    SET str = REPLACE(str,'ù','u');
    SET str = REPLACE(str,'ú','u');
    SET str = REPLACE(str,'û','u');
    SET str = REPLACE(str,'ý','y');
    SET str = REPLACE(str,'ý','y');
    SET str = REPLACE(str,'þ','b');
    SET str = REPLACE(str,'ÿ','y');
    SET str = REPLACE(str,'ƒ','f');


    RETURN str;
END
//
DELIMITER ;

user3245067 · Answer 4 · 2014-01-28T15:31:17.560

drop function if exists fn_remove_accents;
delimiter |
create function fn_remove_accents( textvalue varchar(20000) )
returns varchar(20000)
begin

set @textvalue = textvalue;

-- ACCENTS
set @withaccents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
set @withoutaccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
set @count = length(@withaccents);

while @count > 0 do
    set @textvalue = replace(@textvalue, substring(@withaccents, @count, 1), substring(@withoutaccents, @count, 1));
    set @count = @count - 1;
end while;

-- SPECIAL CHARS
set @special = '!@#$%¨&*()_+=§¹²³£¢¬"`´{[^~}]<,>.:;?/°ºª+*|\\''';
set @count = length(@special);
while @count > 0 do
    set @textvalue = replace(@textvalue, substring(@special, @count, 1), '');
    set @count = @count - 1;
end while;

return @textvalue;

end
|

The solution is great but uses session variables instead of local variables and is missing the DETERMINISTIC keyword. Please see updated solution below... — jfx, Mar 25 '19 at 16:37

score 4 · Answer 5 · edited May 23 '17 at 12:17

Here is an easy solution with a single query :

UPDATE `my_table` SET alias = lower(name),
alias = replace(alias,'Š','S'),
alias = replace(alias,'š','s'),
alias = replace(alias,'Ð','Dj'),
alias = replace(alias,'Ž','Z'),
alias = replace(alias,'ž','z'),
alias = replace(alias,'À','A'),
alias = replace(alias,'Á','A'),
alias = replace(alias,'Â','A'),
alias = replace(alias,'Ã','A'),
alias = replace(alias,'Ä','A'),
alias = replace(alias,'Å','A'),
alias = replace(alias,'Æ','A'),
alias = replace(alias,'Ç','C'),
alias = replace(alias,'È','E'),
alias = replace(alias,'É','E'),
alias = replace(alias,'Ê','E'),
alias = replace(alias,'Ë','E'),
alias = replace(alias,'Ì','I'),
alias = replace(alias,'Í','I'),
alias = replace(alias,'Î','I'),
alias = replace(alias,'Ï','I'),
alias = replace(alias,'Ñ','N'),
alias = replace(alias,'Ò','O'),
alias = replace(alias,'Ó','O'),
alias = replace(alias,'Ô','O'),
alias = replace(alias,'Õ','O'),
alias = replace(alias,'Ö','O'),
alias = replace(alias,'Ø','O'),
alias = replace(alias,'Ù','U'),
alias = replace(alias,'Ú','U'),
alias = replace(alias,'Û','U'),
alias = replace(alias,'Ü','U'),
alias = replace(alias,'Ý','Y'),  
alias = replace(alias,'š','s'),
alias = replace(alias,'Ð','Dj')
alias = replace(alias,'ž','z'),
alias = replace(alias,'Þ','B'),
alias = replace(alias,'ß','Ss'),
alias = replace(alias,'à','a'),
alias = replace(alias,'á','a'),
alias = replace(alias,'â','a'),
alias = replace(alias,'ã','a'),
alias = replace(alias,'ä','a'),
alias = replace(alias,'å','a'),
alias = replace(alias,'æ','a'),
alias = replace(alias,'ç','c'),
alias = replace(alias,'è','e'),
alias = replace(alias,'é','e'),
alias = replace(alias,'ê','e'),
alias = replace(alias,'ë','e'),
alias = replace(alias,'ì','i'),
alias = replace(alias,'í','i'),
alias = replace(alias,'î','i'),
alias = replace(alias,'ï','i'),
alias = replace(alias,'ð','o'),
alias = replace(alias,'ñ','n'),
alias = replace(alias,'ò','o'),
alias = replace(alias,'ó','o'),
alias = replace(alias,'ô','o'),
alias = replace(alias,'õ','o'),
alias = replace(alias,'ö','o'),
alias = replace(alias,'ø','o'),
alias = replace(alias,'ù','u'),
alias = replace(alias,'ú','u'),
alias = replace(alias,'û','u'),
alias = replace(alias,'ý','y'),
alias = replace(alias,'ý','y'),
alias = replace(alias,'þ','b'),
alias = replace(alias,'ÿ','y'),
alias = replace(alias,'ƒ','f'),
alias = replace(alias, 'œ', 'oe'),
alias = trim(alias);

In this example :

'my_table' is the name of the table,
'name' is the original field
'alias' is the new field

Hope it helps !

You can also check this variant if you try to generate a slug field with mysql : Easy way of generating a slug name column from the name column?

This is great! Just quick comments: you're missing a comma after the line "alias = replace(alias,'Ð','Dj')". And you can also add these two extra lines, to catch a few more variations: alias = replace(alias, 'Ł', 'L'), alias = replace(alias, 'ą', 'a'), — Asparagirl, Mar 07 '19 at 20:48

score 3 · Answer 6 · answered Mar 25 '19 at 16:36

The solution of user3245067 is great, but it uses session variables. This is the solution with local variables and also with DETERMINISTIC keyword for mysql 5.5. Tested with german umlaute in mysql-5.5.

drop function if exists fn_remove_accents;
delimiter $$
create function fn_remove_accents( param_textvalue varchar(20000) )
returns varchar(20000) DETERMINISTIC
begin

DECLARE var_textvalue VARCHAR(20000);
DECLARE var_withaccents VARCHAR(50);
DECLARE var_withoutaccents VARCHAR(50);
DECLARE var_count INT;
DECLARE var_special INT;

set var_textvalue = param_textvalue;

-- ACCENTS
set var_withaccents = 'ŠšŽžÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝŸÞàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿþƒ';
set var_withoutaccents = 'SsZzAAAAAAACEEEEIIIINOOOOOOUUUUYYBaaaaaaaceeeeiiiinoooooouuuuyybf';
set var_count = length(var_withaccents);

while var_count > 0 do
    set var_textvalue = replace(var_textvalue, substring(var_withaccents, var_count, 1), substring(var_withoutaccents, var_count, 1));
    set var_count = var_count - 1;
end while;

-- SPECIAL CHARS
set var_special = '!var_#$%¨&*()_+=§¹²³£¢¬"`´{[^~}]<,>.:;?/°ºª+*|\\''';
set var_count = length(var_special);
while var_count > 0 do
    set var_textvalue = replace(var_textvalue, substring(var_special, var_count, 1), '');
    set var_count = var_count - 1;
end while;

return var_textvalue;

end$$

delimiter ;

Hi! Are you sure of your var_special ? Why do you have this pattern : var_ ? — Laurent.B, Apr 29 '20 at 12:43
I use the pattern "var_" to easily distinguish variables and column names - this is especially useful if using selects in the function, but admittedly it's not that valuable in here :). — jfx, Oct 09 '20 at 11:00
I second Laurent.B 's comment. Probably there is misunderstanding, we are puzzled by the content of the var_special , not by its name. Also, I think it should be a string instead of INT . — Vojta Havránek, Feb 21 '22 at 13:41

score 0 · Answer 7 · answered Jun 11 '15 at 01:29

0

I wonder if MariaDB's REGEXP_REPLACE(col, 'e', 'e') with utf8_unicode_ci would do all the es at once.

answered Jun 11 '15 at 01:29

Rick James

135,179
13
127
222

`REGEX` functions normally treats accented characters as unequal with the normal characters. So this will not work – Linga Jul 28 '20 at 14:54
Yes, MySQL 8.0 should work. It is not the case in `MariaDB` yet. – Linga Jul 31 '20 at 02:55

score 0 · Answer 8 · answered Jan 23 '22 at 16:39

This is the fastest and most efficient way I found:

UPDATE movies SET `name` = REPLACE(`name`,'Š','S');
UPDATE movies SET `name` = REPLACE(`name`,'š','s');
UPDATE movies SET `name` = REPLACE(`name`,'Ð','Dj');
UPDATE movies SET `name` = REPLACE(`name`,'Ž','Z');
UPDATE movies SET `name` = REPLACE(`name`,'ž','z');
UPDATE movies SET `name` = REPLACE(`name`,'À','A');
UPDATE movies SET `name` = REPLACE(`name`,'Á','A');
UPDATE movies SET `name` = REPLACE(`name`,'Â','A');
UPDATE movies SET `name` = REPLACE(`name`,'Ã','A');
UPDATE movies SET `name` = REPLACE(`name`,'Ä','A');
UPDATE movies SET `name` = REPLACE(`name`,'Å','A');
UPDATE movies SET `name` = REPLACE(`name`,'Æ','A');
UPDATE movies SET `name` = REPLACE(`name`,'Ç','C');
UPDATE movies SET `name` = REPLACE(`name`,'È','E');
UPDATE movies SET `name` = REPLACE(`name`,'É','E');
UPDATE movies SET `name` = REPLACE(`name`,'Ê','E');
UPDATE movies SET `name` = REPLACE(`name`,'Ë','E');
UPDATE movies SET `name` = REPLACE(`name`,'Ì','I');
UPDATE movies SET `name` = REPLACE(`name`,'Í','I');
UPDATE movies SET `name` = REPLACE(`name`,'Î','I');
UPDATE movies SET `name` = REPLACE(`name`,'Ï','I');
UPDATE movies SET `name` = REPLACE(`name`,'Ñ','N');
UPDATE movies SET `name` = REPLACE(`name`,'Ò','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ó','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ô','O');
UPDATE movies SET `name` = REPLACE(`name`,'Õ','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ö','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ø','O');
UPDATE movies SET `name` = REPLACE(`name`,'Ù','U');
UPDATE movies SET `name` = REPLACE(`name`,'Ú','U');
UPDATE movies SET `name` = REPLACE(`name`,'Û','U');
UPDATE movies SET `name` = REPLACE(`name`,'Ü','U');
UPDATE movies SET `name` = REPLACE(`name`,'Ý','Y');
UPDATE movies SET `name` = REPLACE(`name`,'Þ','B');
UPDATE movies SET `name` = REPLACE(`name`,'ß','Ss');
UPDATE movies SET `name` = REPLACE(`name`,'à','a');
UPDATE movies SET `name` = REPLACE(`name`,'á','a');
UPDATE movies SET `name` = REPLACE(`name`,'â','a');
UPDATE movies SET `name` = REPLACE(`name`,'ã','a');
UPDATE movies SET `name` = REPLACE(`name`,'ä','a');
UPDATE movies SET `name` = REPLACE(`name`,'å','a');
UPDATE movies SET `name` = REPLACE(`name`,'æ','a');
UPDATE movies SET `name` = REPLACE(`name`,'ç','c');
UPDATE movies SET `name` = REPLACE(`name`,'è','e');
UPDATE movies SET `name` = REPLACE(`name`,'é','e');
UPDATE movies SET `name` = REPLACE(`name`,'ê','e');
UPDATE movies SET `name` = REPLACE(`name`,'ë','e');
UPDATE movies SET `name` = REPLACE(`name`,'ì','i');
UPDATE movies SET `name` = REPLACE(`name`,'í','i');
UPDATE movies SET `name` = REPLACE(`name`,'î','i');
UPDATE movies SET `name` = REPLACE(`name`,'ï','i');
UPDATE movies SET `name` = REPLACE(`name`,'ð','o');
UPDATE movies SET `name` = REPLACE(`name`,'ñ','n');
UPDATE movies SET `name` = REPLACE(`name`,'ò','o');
UPDATE movies SET `name` = REPLACE(`name`,'ó','o');
UPDATE movies SET `name` = REPLACE(`name`,'ô','o');
UPDATE movies SET `name` = REPLACE(`name`,'õ','o');
UPDATE movies SET `name` = REPLACE(`name`,'ö','o');
UPDATE movies SET `name` = REPLACE(`name`,'ø','o');
UPDATE movies SET `name` = REPLACE(`name`,'ù','u');
UPDATE movies SET `name` = REPLACE(`name`,'ú','u');
UPDATE movies SET `name` = REPLACE(`name`,'û','u');
UPDATE movies SET `name` = REPLACE(`name`,'ý','y');
UPDATE movies SET `name` = REPLACE(`name`,'ý','y');
UPDATE movies SET `name` = REPLACE(`name`,'þ','b');
UPDATE movies SET `name` = REPLACE(`name`,'ÿ','y');
UPDATE movies SET `name` = REPLACE(`name`,'ƒ','f');

score -4 · Answer 9 · answered Jan 27 '11 at 07:13

-4

Check out the MySQL manual on CONVERT() and CAST(): http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

CONVERT() provides a way to convert data between different character sets. The syntax is:

CONVERT(expr USING transcoding_name)

answered Jan 27 '11 at 07:13

Piskvor left the building

91,498
46
177
222

2

CONVERT and CAST don't translit characters – Marco Marsala Aug 04 '14 at 22:49

How to remove accents in MySQL?

9 Answers9

Linked