Función translate en mysql

[huesos52]

Hoy tuve un problema con una consulta que muchas veces se ha presentado en este foro y en el mundo en general.

Como realizar una consulta en la cual, no me distinga las palabras con acento en una consulta. La solución para mysql es pelear con la molesta codificación de la tabla o de la base de datos en general.

Encontré una solución muy particular y efectiva para oracle y postgres haciendo uso de la función translate.
http://www.postgresql.org/docs/8.2/s...ns-string.html
La cual basta con realizar translate(campo,'áéíóúÁÉÍÓÚ','aeiouAEIOU') para ignorar las palabras que llevan acento y permitir una consulta tanto de palabras que tienen o no acento.

Lastimosamente mysql no cuenta con una función así (o por lo menos no la encontré) así que decidí crear una que simule en parte este molesto problema.

Comparto la función, por si alguien desea usarla y la forma como deben implementarla. (Acepto sugerencias y comentarios al respecto)


EJEMPLO

Código mysql:

mysql> select nombre,sexo from persona;
+----------------+------+
| nombre         | sexo |
+----------------+------+
| maría fernanda | F    |
| maria lucia    | F    |
| lucía          | F    |
+----------------+------+
3 rows in set (0.00 sec)
 

Como ven, en los registros, maria y lucia se encuentran con acento y sin acento. Para una consulta de personas llamadas lucia o maria quedaría:

Código mysql:

mysql> select nombre,sexo from persona where translate(lower(nombre)) like translate('%maria%');
+----------------+------+
| nombre         | sexo |
+----------------+------+
| maría fernanda | F    |
| maria lucia    | F    |
+----------------+------+
2 rows in set (0.00 sec)
 
mysql> select nombre,sexo from persona where translate(lower(nombre)) like translate('%lucía%');
+-------------+------+
| nombre      | sexo |
+-------------+------+
| maria lucia | F    |
| lucía       | F    |
+-------------+------+
2 rows in set (0.00 sec)
 

La función es esta:

Código mysql:

DROP FUNCTION translate; 
DELIMITER $$
 
CREATE FUNCTION translate(campo varchar(50)) RETURNS VARCHAR(50)
BEGIN
----------------------------------------
--Declaración de variables requeridas---
----------------------------------------
 
--------------------------------------------------------------------
--* Longitud guarda el numero de digitos de la palabra ingresada. -- 
--* contador recorre cada posición para comparar.                 --  
--* campo_aux recibe el valor de cada letra,se haya cambiado o no.--
--* campo_mod es la cadena a retornar. Con ls correcciones.       --
--------------------------------------------------------------------
 DECLARE longitud integer;     
 DECLARE contador integer;
 DECLARE campo_aux varchar(50);       
 DECLARE campo_mod varchar(50);     
 
--------------------------------
--Inicialización de variables---
-------------------------------- 
 set campo_mod = '';
 set campo_aux = '';
 set contador=1;
 set longitud=length(campo);
 
---------------------------------------------------------------------------------------
--En el while se compara cada posición de la palabra y verifica si tiene acento o no.--
--Si tiene acento, lo cambia. sino tiene acento, lo deja tal como estaba.            --
---------------------------------------------------------------------------------------
  WHILE longitud >= contador DO    
   if mid(lower(campo),contador,1) = 'á' then
     set campo_aux = 'a';
 
   elseif mid(lower(campo),contador,1) = 'é' then
     set campo_aux = 'e'; 
 
   elseif mid(lower(campo),contador,1) = 'í' then
     set campo_aux = 'i';
 
   elseif mid(lower(campo),contador,1) = 'ó' then
     set campo_aux = 'o';
 
   elseif mid(lower(campo),contador,1) = 'ú' then
     set campo_aux = 'u';
   else
     set campo_aux = mid(lower(campo),contador,1);
  end if;
----------------------------------------------------------------
--En campo_mod se va creando la cadena resultante sin acentos.--
--Contador se incrementa para pasar a la siguiente posición.  --
----------------------------------------------------------------
  set campo_mod = concat(campo_mod,campo_aux);
  set contador = contador + 1;
 end while;
 
return campo_mod;
end$$
delimiter ;
 

Espero les sirva.

[gnzsoloyo]

En MySQL el problema se resuelve simplemente:
1. DEfiniendo la tabla con CHARSET latin1 y COLLATION latin1_swedish_ci
2. Asignando al campo la conversión con COLLATION latin1_swedish_ci.
De ese modo el problema se resuelve por tabla de caracteres.
Prueba esto:

Código sql:

CREATE TABLE  `nombres` (
  `NOMBRE` VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
INSERT INTO NOMBRES VALUES('MARIA LUCÍA'), ('LUCIA'), ('MARÍA'),('MARÍA FERNANDA');
 
SELECT * 
FROM nombres n 
WHERE NOMBRE LIKE'%MARIA%';
 

Eso te debería devolver los tres registros que cumplen la condición, sin importar si MARIA está acentuada o no....

[Anubis_Slash]

Hola

No cabe duda que todos los dias se aprende algo nuevo, muy bueno lo de huesos52 cuando ya esta creada la tabla, pero si se puede evitar eso desde el diseño como nos dice gnzsoloyo mucho mejor...

son unos crack...

saludos

[huesos52]

gnzsoloyo... te cuento que no me ha funcionado.

Código mysql:

mysql> CREATE TABLE  `nombres` (
    ->   `NOMBRE` varchar(100) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected, 1 warning (0.04 sec)
 
mysql> INSERT INTO NOMBRES VALUES('MARIA LUCÍA'), ('LUCIA'), ('MARÍA'),('MARÍA FERNANDA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT *
    -> FROM nombres n
    -> WHERE NOMBRE LIKE'%MARIA%';
+-------------+
| NOMBRE      |
+-------------+
| MARIA LUCÍA |
+-------------+
1 row in set (0.00 sec)
 

Es posible que hace falta algo pequeño. No estoy diciendo que no exista solución para este problema, pero por eso digo... muchas veces para algo tan simple como una simple diferenciación de acentos, tenemos que hacer variaciones en la collation y charset de las tablas y campos. sumando el tiempo que puede conllevar entender las diferencias entre cada uno de los tipos de codificación que existen. Por mi parte, he tenido bastantes problemas en este sentido, que he solucionado después de tediosas pruebas y cambios a mi base de datos.

No está de mas, contar ahora con una función que resuelva este problema en una simple ejecución.

[gnzsoloyo]

No está de mas, contar ahora con una función que resuelva este problema en una simple ejecución.

Es posible, pero usar una función de ese tipo en consultas contra centenares de miles de registros en una base, puede reducir la performance bastante...

Vamos a hacer un último intento:

Código sql:

CREATE TABLE  `nombres` (
  `NOMBRE` VARCHAR(100) DEFAULT NULL COLLATE latin1_spanish_ci
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_spanish_ci;
 
INSERT INTO NOMBRES VALUES('MARIA LUCÍA'), ('LUCIA'), ('MARÍA'),('MARÍA FERNANDA');
 
SELECT *
FROM nombres n
WHERE NOMBRE LIKE _latin1'%lucia%' COLLATE latin1_spanish_ci;
 

[huesos52]

Nada gnzsoloyo

Código mysql:

mysql> drop table nombres;
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE  `nombres` (
    ->   `NOMBRE` varchar(100) DEFAULT NULL COLLATE latin1_spanish_ci
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_spanish_ci;
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
mysql> INSERT INTO NOMBRES VALUES('MARIA LUCÍA'), ('LUCIA'), ('MARÍA'),('MARÍA FERNANDA');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT *
    -> FROM nombres n
    -> WHERE NOMBRE LIKE _latin1'%lucia%' COLLATE latin1_spanish_ci;
+--------+
| NOMBRE |
+--------+
| LUCIA  |
+--------+
1 row in set (0.00 sec)
 
mysql>
 

Estoy totalmente de acuerdo, para bastantes registros, puede presentar prooblemas de performance.
Podría existir alguna forma de optimizarla un poco?

[huesos52]

repetido..

[huesos52]

Tenias razón gnzsoloyo

El rendimiento se ve seriamente afectado en un numero excesivo de registros.

hice pruebas de rendimiento en una tabla con 240000 registros, comparando el rendimiento de
las consultas haciendo uso de la función y sin hacer uso de la función.

Para 10000 registros
Con función: 10 segundos.
Sin función: 07 segundos.

Para 50000 registros
Con función: 55 segundos.
Sin función: 34 segundos.

Para 100000 registros
Con función: 106 segundos.
Sin función: 068 segundos.

Para 200000 registros
Con función: 212 segundos.
Sin función: 135 segundos.

En conclusión, Para consultas con excesivo numero de registros, la función queda obsoleta, sin embargo, para consultas que no superen los 50000 registros, me parece que es un tiempo considerable de espera.

un saludo y espero alguien la use, a mi me va perfecto.

[jurena]

amigo huesos52,
yo soy de la opinión de gnzsoloyo.
dices

Es posible que hace falta algo pequeño

Creo que lo que falta es que la base no tiene la codificación adecuada; sí las tablas, pero no la base.
Crea una base nueva con codificación latin1, y collation latin1_spanish_ci; y dentro de ella, crea tu tabla tal y como la tienes. Luego haz las búsquedas. Yo lo he probado y funciona perfectamente.
Creo que casi todos los problemas de este tipo se resuelven con la codificación, el charset; el problema es que (y lo digo por mí) creamos bases sin estudiar nuestras necesidades futuras y sin pensar en la mejor codificación, y aceptamos la codificación y collation por defecto: la famosa latin1_swedish_ci, pero ninguno hacemos las comparaciones en sueco, que yo sepa. Piensa que cuando eliges eso en el PHPMyAdmin estás eligiendo la collation latin1_swedish_ci, y el charset latin1 (el equivalente al ISO-8859-1) para la base y para las conexiones. Aunque luego codifiques las tablas bien (imagenemos que quieres utf8), ya podrías, creo, tener problemas.
Intervienen otros factores sobre la codificación, como el hecho de la codificación que se envía a través del navegador al servidor y cómo la devuelve (las conexiones). Los archivos de programación y los de html tienen mucho que ver también, e incluso si se pasa un dato por el método POST o GET, tendrá cierta influencia sobre la codificación cuando pase por el navegador, al menos eso he leído.

[huesos52]

jejeje...

Jurena... creeme, yo también soy de la opinión de gnzsoloyo. Solucionar estos problemas adecuadamente, es la mejor opción que puede existir a dicho problema de acentuaciones.

En el pasado, he tenido estos problemas, y los he solucionado con opiniones tuyas (en gran mayoría) de los foros y todo lo que recomiendas como tal.

el problema es que (y lo digo por mí) creamos bases sin estudiar nuestras necesidades futuras y sin pensar en la mejor codificación

Creeme, esto nos pasa a todos. Son pocas las veces que pensamos en esto, antes de
realizar un correcto diseño de las bases de datos.

He notado que en todos los post que se relacionan con este problema, la solución se da despues del mensaje 20 y la opinión de unos cuantos cristianos jejeje.

La verdad, me causó mucha intriga que este problema en oracle, la solución que mas recomiendan es el uso de la función translate. El objetivo de esta función, no es asegurar que es mejor usar este método para solucionar el problema, simplemente tiene como objetivo facilitar a algunos usuarios que no tenemos dominio en el tema de charset y collation para implementar una solución alternativa. Por que aceptemoslo, es un tema bien maluco.

Un saludo a todos y gracias por sus comentarios.

[jurena]

Estoy de acuerdo contigo, huesos52, se trata de "un tema bien maluco" (permíteme que emplee tu expresión: me ha gustado), principalmente porque asignamos al azar las codificaciones, y luego porque cuando almacenamos algo en una codificación queremos que pulsando un botón de cambio se resuelva todo: y eso no es así. Nos preocupa tener que darle la vuelta a la codificación y no queremos dedicar ni un minuto a hacer ese esfuerzo, máxime cuando el problema de codificación afecta no sólo a la base.
Saludos y gracias a ti y a todos los que nos formáis cada día en este foro.

[Genetix]

Hola
Como bien dicen es un dolor de cabeza, por lo general es recomendable (o por lo menos eso es mi premisa) de usar todo en utf-8 que es lo mas universal que existe, y evita muchos dolores de cabeza.

Pero hay casos en que la data puede estar determinada en otros tipos de codificación, para eso suelo usar el set names con el cual se puede jugar con lo que se envía y recibe del servidor sin tocar las tablas ni la DB.

ejemplo

Código sql:

mysql> SET NAMES UTF8;
Query OK, 0 ROWS affected (0.00 sec)
 
mysql> SELECT * 
    -> FROM nombres n 
    -> WHERE NOMBRE LIKE'%MARIA%';
 
+----------------+
| NOMBRE         |
+----------------+
| MARIA LUCÍA    | 
| MARÍA          | 
| MARÍA FERNANDA | 
+----------------+
3 ROWS IN SET (0.00 sec)
 

Hay mas comandos que se pueden usar
http://dev.mysql.com/doc/refman/5.0/...onnection.html
Para manipular lo que se envía y recibe.

Saludos!