Búsquedas en Oracle10g case-insensitive

1. Objetivo

El objetivo de este documento es mostrar cómo parametrizar la base de datos oracle para que se puedan realizar búsquedas en una base de datos ORACLE 10g, siendo sensible esta búsqueda a mayúsculas y a minúsculas y distinguiendo también los acentos.

2. Introducción

Las operaciones dentro de una base de datos oracle siempre son sensibles a mayúsculas, minúsculas y a los acentos. Algunas veces es necesario realizar búsquedas que no sean sensibles a mayúsculas y minúsculas y realizar comparaciones sin tener en cuenta los acentos.

En las versiones anteriores de base de datos, este “problema” se tenía que solventar utilizando las funciones NLS_UPPER y NLS_LOWER . Estas funciones cambiaban la palabra buscada de mayúsculas a minúsculas (o viceversa) para poder realizar este tipo de búsquedas.

Estas dos funciones  pueden ser incómodas a la hora de utilizarlas puesto que necesitan usarse en la lógica del programa en casa búsqueda. Una solución parcial a este problema se obtuvo en la versión ORACLE9i Release 2(9.2), usando la búsqueda mediante GENERIC_BASELETTER el cual agrupa todos los caracteres basados en el “valor base”.

3. Ejemplo de búsquedas en Oracle 10g

Pongamos un ejemplo para ver cómo realizar esta búsqueda pudiendo distinguir entre mayúsculas, minúsculas y acentos. Vamos a crear una tabla denominada persona con un único atributo denominado nombre y vamos a introducir los siguientes datos:

CREATE TABLE persona (nombre VARCHAR2(20));
INSERT INTO persona VALUES (‘RAMON’);
INSERT INTO persona VALUES (‘Ramón’);
INSERT INTO persona VALUES (‘RAMon’);
INSERT INTO persona VALUES (‘ramón’);
INSERT INTO persona VALUES (‘ramon’);

Ahora con los valores por defecto que tiene la base de datos realizamos una búsqueda:

SELECT nombre FROM persona where nombre = ‘RAMON’
-------------------------------------------------
RAMON

Como observamos, en este caso no se ha hecho distinción entre mayúsculas y minúsculas y sólo se obtiene el resultado idéntico a nuestra comparación. Como hemos dicho en algunos casos es necesario obtener todos los demás datos sin tener en cuenta si es mayúscula o minúscula o que contenga acentos. Para ellos vamos a realizar las siguientes modificaciones en nuestra sesión:

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

Una vez realizados estos cambios realizamos la misma consulta que hemos hecho antes:

SELECT nombre FROM persona where nombre = ‘RAMON’
-------------------------------------------------
RAMON
Ramón
RAMon
ramón
ramon

Todas estas anotaciones funcionan cuando utilizamos expresiones de igualdad, si queremos utilizar el operador like, lo anteriormente explicado no sirve puesto que no está soportado.
No obstante si queremos realizar búsquedas utilizando like y que sean case- insensitive habría que utilizar la función REGEXP_LIKE con las modificaciones anteriormente explicadas.

Ejemplo de regexp_like:

SELECT  nombre FROM persona WHERE regexp_like ( name,’Ramon’);

 

About: admin


14 thoughts on “Búsquedas en Oracle10g case-insensitive”

  1. Después del Primer Select Se Escribió :

    – Como observamos, en este caso no se ha hecho distinción entre mayúsculas y minúsculas y sólo se obtiene el resultado idéntico a nuestra comparación.

    Debería Ser :

    – Como observamos, en este caso SI se ha hecho distinción entre mayúsculas y minúsculas y sólo se obtiene el resultado idéntico a nuestra comparación.

    Si no Se Hiciera distinción Seleccionaría Todo.

    Aparte de ese pequeño detalle, me pareció excelente el Tip

  2. Hola, una pregunta

    ¿Habría alguna forma de recuperar en una consulta todos los valores distintos a excepción de aquellos que se diferecien en mayusculas o minusculas o por los acentos?

    Me explico:

    Si yo tengo: Ramón

    ramon

    RAMON

    RANON

    que el resultado de la búsqueda fuese RANON.

    Muchas gracias.

  3. He aplicado esto en Oracle 10g añadiendo un índice de esta manera:

    create index

    IX_USUARIOS_NICK

    on

    USUARIOS

    (

    nlssort( NICK, ‘NLS_SORT=BINARY_CI’)

    )

    Luego las consultas case-insensitive las hago ejecutando antes «ALTER SESSION SET NLS_COMP=ANSI» dentro de una transacción. En coldfusion así:

    ALTER SESSION SET NLS_COMP=ANSI

    ALTER SESSION SET NLS_SORT=BINARY_CI

    SELECT *

    FROM USUARIOS

    WHERE NICK = ‘ramon’

    Las primeras 2 consultas dentro de la transacción se pueden omitir dependiendo si queremos filtrar, ordenar o ambas cosas.

  4. Pregunta

    Pero no existe ninguna funcion en oracle para :

    1-.Una base de datos quede como insensitve

    o

    2-.Una tabla especifica quede insensitive

    Un comando o swiche, que se le mueva la base datos a las tabas?

    Gracias por su respuestas

  5. Por qué no utiliza

    SELECT nombre FROM persona WHERE regexp_like ( name,’Ramon’, ‘ i ‘ ) ;

    Con eso realiza busquedas Case-insensitive sin necesidad de modificar la BD

  6. Ahí tienes que ver qué valoras más, el no cambiar la base de datos o la rapidez que te da el índice (con el regexp_like no puedes usar índices).

  7. Disculapa pero…

    en donde se de poner…

    ALTER SESSION SET NLS_COMP=ANSI;

    ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

    no entiendo al principio d la consula o donde,

    soy nueva mul disculpas

  8. Hola hor.

    Si lo haces directamente en Oracle o en el TOAD, simplemente ejecuta esas dos líneas antes de tu select.

    ALTER SESSION SET NLS_COMP=ANSI;

    ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

    SELECT nombre FROM persona WHERE regexp_like ( name,’Ramon’);

    Si lo haces desde php, por ejemplo, tendrás que hacerlo dentro de una transacción.

  9. Hola!

    Tengo una duda … como puedo dejar esas variables en el sistema ? es decir, no quiero estalar ejecutando cada vez que prendo la pc, si no que ya esten por default… probe con esto:

    ALTER SYSTEM SET NLS_COMP=ANSI scope=spfile;
    ALTER SYSTEM SET NLS_SORT=GENERIC_BASELETTER scope=spfile;

    Y me dice que el archivo se modifico, pero cuando reinicio el servidor de BD Oracle, hago la consulta :

    SELECT nombre FROM persona WHERE regexp_like ( name,’Ramon’);

    y no me regresa nada, que puedo hacer entonces ??

    Gracias 🙂

  10. Hola.

    No es muy recomendable setear esas variables para todo el sistema. Las comparaciones ANSI cuestan más en las consultas, por eso sólo se deberían setear en el momento en que se usen.

    Aún así, si lo quieres siempre, setearlas en el spfile sería lo correcto. No sé por qué no te funciona. Si te atrancas con ello, también puedes setearlas por session usando un trigger cuando el cliente se conecte.

    Un saludo.

  11. ¿Existe la posibilidad de hacer una consulta insensible con una función agregada? Se trataría de hacer algo así:

    CREATE TABLE persona (nombre VARCHAR2(20), sueldo number(4));
    INSERT INTO persona VALUES (‘RAMON’, 1000);
    INSERT INTO persona VALUES (‘Ramón’, 1000);
    INSERT INTO persona VALUES (‘RAMon’, 1000);
    INSERT INTO persona VALUES (‘ramón’, 1000);
    INSERT INTO persona VALUES (‘ramon’, 1000);

    Y que al hacer esta consulta:

    select nombre, sum(sueldo) «total»
    from persona
    group by nombre

    devolviera esto:

    nombre total
    ————————–
    RAMON 5000

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *