mysql - quitar - replace accents sql server
¿Cómo eliminar acentos en MySQL? (7)
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?
Acabo de compilar 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?
Consulte el manual de MySQL en CONVERT() y CAST() : http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html
CONVERT () proporciona una forma de convertir datos entre diferentes conjuntos de caracteres. La sintaxis es:
CONVERT(expr USING transcoding_name)
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.
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 ;
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);
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
|