¿Cómo eliminar acentos en MySQL?

Acabo de comstackr una base de datos de 1 millón de nombres de lugares. Voy a usarlo en un widget de autocompletar para buscar ciudades. Muchos de estos lugares tienen acentos … Quiero poder encontrar registros cuando un usuario escribe el nombre sin acento.

Para hacer esto, tengo una segunda columna con una copia sin acentos del nombre. Muchos de estos registros aún están en blanco, así que quiero escribir una consulta para completarlos. ¿Es esto posible en MySQL? ¿Si es así, cómo?

Si establece una intercalación apropiada para la columna, el valor dentro del campo se comparará naturalmente con su equivalente no acentuado.

 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) 

Tuve el mismo problema, así que escribí una lista de consultas basadas en un script PHP. Tengo que eliminar acentos y hacer URLs amigables con el SEO:

Tal vez le gustaría agregar otros caracteres especiales, como los símbolos $ o £

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

Puedo compartir esto, tal vez pueda ayudar …:

 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 | 

Aquí hay una solución fácil con una sola consulta:

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

En este ejemplo :

  • ‘my_table’ es el nombre de la tabla,
  • ‘nombre’ es el campo original
  • ‘alias’ es el nuevo campo

Espero eso ayude !

También puede consultar esta variante si intenta generar un campo slug con mysql: ¿ forma fácil de generar una columna de nombre de slug a partir de la columna de nombre?

Me pregunto si el REGEXP_REPLACE(col, 'e', 'e') de MariaDB REGEXP_REPLACE(col, 'e', 'e') con utf8_unicode_ci haría todas las ees a la vez.

    Intereting Posts