Buscar texto ignorando acentos en Oracle

No suele ser complicado realizar búsquedas en campos de texto o varchar en Oracle. La mayoría de veces, con un simple like y jugando un poco con los comodines suele ser suficiente.

Utilizando el porcentaje (%) como comodín para cualquier cadena o guión bajo (_) podemos localizar cadenas que comiencen, terminen o, simplemente, contengan el fragmento que buscamos. Otro tema sería el tema de la optimización de este tipo de búsquedas, ya que suelen utilizar los índices sólo si usamos el like para buscar cadenas que comiencen por lo que estamos buscando.

Un problema que nos podemos encontrar es que Oracle distingue entre mayúsculas y minúsculas. Por suerte, esto tiene fácil solución. Basta con pasar tanto el campo donde se busca como el texto buscado, bien a minúsculas -con la función LOWER(…)-, o bien a mayúsculas – con la función UPPER-.

Pero todo esto se complica cuando buscamos cadenas en idiomas como el castellano, el catalán o el francés, los cuales contienen palabras con tilde, lo cual afecta a los resultados de la búsqueda.

Función TRANSLATE

Una posible solución, sería utilizar la función translate. A esta función se le pasa el campo sobre el cual queremos hacer la transformación para sustuir los caracteres del segundo parámetro (las vocales con tilde) por los caracteres del tercer parámetro (vocales sin tilde). Carácter a carácter en el mismo orden.

translate(campo, ‘áéíóúÁÉÍÓÚ’, ‘aeiouAEIOU’)

Hay que tener en cuenta que, en algunos idiomas como el català, hay acentos abiertos (hacia la izquierda) y cerrados (hacia la derecha), por lo que habría que mejorar esta solución.

Uso de COLLATE

Otra solución, un poco más clara y limpia, sería hacer uso de collate. Fue añadida a partir de la versión 12.2 de Oracle y es muy útil para este tipo de casos. Se añade justo después del like donde lo queramos usar, de la siguiente manera.

SELECT * FROM tabla1
WHERE tabla1.texto like '%' || :texto || '%'  collate binary_ai

Donde el valor del atributo collate sería <tipo_collation>{_(CI|AI)}:

• CI -> case-insensitive, accent-sensitive. Es decir, no tiene en cuenta mayúsculas/minúsculas pero sí los acentos o tildes.
• AI -> accent- and case-insensitive. Es decir, no tiene en cuenta mayúsculas/minúsculas ni los acentos o tildes.

Así, usando binary_ci o binary_ai según lo que nos interese, sería suficiente.

Ordenar por una columna case-insensitive

De la misma manera, podemos en una consulta, ordenar por una columna sin tener en cuenta los acentos o las mayúsculas. De la siguiente manera:

Select campo1, campo2
from tabla1
order by campo1
collate binary_ai

Ignorar acentos y mayúsculas en Oracle 12.1 y anteriores

Antes de la versión 12.2 ya existía el parámetro collate, pero no era tan sencillo como aplicarlo en la columna que nos interesa directamente. Así que ha sido un gran avance que podemos aprovechar. Pero, por desgracia, nos podemos encontrar con bases de datos con versiones obsoletas que todavía no hayan migrado a versiones más modernas de Oracle.

Por suerte, también hay solución para estas bases de datos. Lo único que la solución pasa por modificar la configuración del motor de base de datos. Se puede modificar para una sesión donde queramos realizar este tipo de búsquedas:

alter session set nls_sort = binary_ai;
alter session set nls_comp = linguistic;

O bien, para toda la base de datos en general, previo reinicio de la instancia:

alter system set nls_sort = binary_ai scope = spfile;
alter system set nls_comp = linguistic scope = spfile;

Uso de collate en índices

Esta característica también se puede utilizar en índices. De igual manera, es distinta la forma de utilizarlo dependiendo de la versión de Oracle utilizada.

En versiones 12.1 o anteriores, el uso sería similar al siguiente.

create index empleados_idx
  on empleados ( 
    nlssort ( nombre, 'nls_sort = binary_ai') 
  );

Mientras que, en versiones más reciente como son de la 12.2 en adelante, la manera de hacerlo sería así.

create index empleados_idx
  on empleados ( 
    nombre 
      collate binary_ci 
  );

Como se ve, ahora es más sencillo aunque no varía mucho.