Comment supprimer les accents en MySQL?

je viens de compiler une base de données d'un million de noms de lieux. Je vais l'utiliser dans un widget automatique pour chercher des villes. Beaucoup de ces endroits ont des accents... Je veux être en mesure de trouver des enregistrements lorsqu'un utilisateur tape le nom sans accent.

pour ce faire, j'ai un 2ème colonne avec une simple copie de ce nom. Beaucoup de ces enregistrements sont encore Vierges, donc je veux écrire une requête pour les remplir. Est-ce possible tout de MySQL? Si oui, comment?

22
demandé sur mpen 2011-01-27 10:04:37

7 réponses

si vous définissez une collation appropriée pour la colonne alors la valeur dans le champ sera comparée égale à son équivalent non accentué naturellement. 

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)
26
répondu Ignacio Vazquez-Abrams 2011-01-27 07:09:54

j'ai eu le même problème, donc j'ai écrit une liste de querys basés sur un script PHP je dois supprimer les accents et rendre les URLs SEO conviviales:

vous aimeriez peut-être ajouter d'autres caractères spéciaux, comme les symboles $ ou £ ... 

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);
20
répondu igasparetto 2017-02-20 14:28:14

je partage cela peut peut-être aider....: 

DROP FUNCTION IF EXISTS `remove_accents`;

DELIMITER //
CREATE 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 ;
11
répondu Abdel 2013-06-11 13:22:14 
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
|
9
répondu user3245067 2014-01-28 15:31:17

Voici une solution facile avec une seule requête : 

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

dans cet exemple:

  • 'ma_table' est le nom de la table,
  • " nom " est le champ d'origine
  • 'alias' est le nouveau champ

Espère que cela aide !

vous pouvez également vérifier cette variante si vous essayez de générer un champ slug avec mysql : manière facile de la génération d'une limace colonne nom de la colonne nom?

2
répondu Erwan 2017-05-23 12:17:26

je me demande si REGEXP_REPLACE(col, 'e', 'e') de MariaDB avec utf8_unicode_ci ferait tous les e à la fois.

0
répondu Rick James 2015-06-11 01:29:13

consultez le manuel MySQL sur CONVERT() et CAST() : http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

CONVERT () fournit un moyen de convertir des données entre différents jeux de caractères. La syntaxe est:

CONVERT(expr USING transcoding_name)

-2
répondu Piskvor 2011-01-27 07:13:42