Wie entfernt man Akzente in MySQL?

Ich habe gerade eine database mit 1 Million Ortsnamen erstellt. Ich werde es in einem Auto-Vervollständigen-Widget verwenden, um Städte nachzuschlagen. Viele dieser Orte haben Akzente … Ich möchte Datensätze finden können, wenn ein Benutzer den Namen ohne einen Akzent eingibt.

Um dies zu tun, habe ich eine zweite Spalte mit einer unbetonten Kopie des Namens. Viele dieser Datensätze sind noch leer, daher möchte ich eine Abfrage schreiben, um sie auszufüllen. Ist dies in MySQL möglich? Wenn das so ist, wie?

Wenn Sie eine geeignete Sortierung für die Spalte festlegen, wird der Wert innerhalb des Felds natürlich mit seinem nicht akzentuierten Äquivalent verglichen.

 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) 

Ich hatte das gleiche Problem, also schrieb ich eine Liste von Querys auf der Basis eines PHP-Skripts. Ich musste Akzente entfernen und SEO-freundliche URLs erstellen:

Vielleicht möchten Sie andere Sonderzeichen hinzufügen, z. B. die Symbole $ oder £

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

Ich teile das vielleicht kann helfen ….:

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

Hier ist eine einfache Lösung mit einer einzigen Abfrage:

 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 diesem Beispiel:

  • ‘my_table’ ist der Name der Tabelle,
  • ‘name’ ist das ursprüngliche Feld
  • ‘Alias’ ist das neue Feld

Ich hoffe es hilft !

Sie können diese Variante auch überprüfen, wenn Sie versuchen, ein Slug-Feld mit mysql zu generieren: Einfache Möglichkeit, eine Slug-Name-Spalte aus der Name-Spalte zu generieren ?

Ich frage mich, ob REGEXP_REPLACE(col, 'e', 'e') mit utf8_unicode_ci alle e s gleichzeitig machen würde.

Schauen Sie sich das MySQL-Handbuch zu CONVERT() und CAST() : http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

CONVERT () bietet eine Möglichkeit, Daten zwischen verschiedenen Zeichensätzen zu konvertieren. Die Syntax lautet:

CONVERT(expr USING transcoding_name)