sql - minusculas - Búsqueda insensible de mayúsculas y minúsculas en Oracle
sql distingue entre mayúsculas y minúsculas (6)
El comportamiento predeterminado de LIKE y los otros operadores de comparación, = etc distingue entre mayúsculas y minúsculas.
¿Es posible hacerlos insensibles a las mayúsculas?
Desde 10gR2, Oracle permite ajustar el comportamiento de las comparaciones de cadenas configurando los parámetros de sesión NLS_COMP y NLS_SORT :
SQL> SET HEADING OFF
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN (''NLS_COMP'', ''NLS_SORT'');
NLS_SORT
BINARY
NLS_COMP
BINARY
SQL>
SQL> SELECT CASE WHEN ''abc''=''ABC'' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
0
SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
Session altered.
SQL>
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN (''NLS_COMP'', ''NLS_SORT'');
NLS_SORT
BINARY_CI
NLS_COMP
LINGUISTIC
SQL>
SQL> SELECT CASE WHEN ''abc''=''ABC'' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
1
También puede crear índices que no distinguen entre mayúsculas y minúsculas:
create index
nlsci1_gen_person
on
MY_PERSON
(NLSSORT
(PERSON_LAST_NAME, ''NLS_SORT=BINARY_CI'')
)
;
Esta información fue tomada de búsquedas insensibles al caso de Oracle . El artículo menciona REGEXP_LIKE pero parece funcionar también con good old = .
En versiones anteriores a 10gR2 no se puede hacer realmente y el enfoque habitual, si no necesita una búsqueda sin acento , es UPPER() tanto la columna como la expresión de búsqueda.
Desde Oracle 12c R2 puedes usar el COLLATE operator :
El operador COLLATE determina la intercalación para una expresión. Este operador le permite anular la intercalación que la base de datos habría derivado para la expresión utilizando reglas de derivación de intercalación estándar.
El operador COLLATE toma un argumento, collation_name, para el que puede especificar una intercalación nombrada o pseudo-intercalación. Si el nombre de la colación contiene un espacio, debe incluir el nombre entre comillas dobles.
Manifestación:
CREATE TABLE tab1(i INT PRIMARY KEY, name VARCHAR2(100));
INSERT INTO tab1(i, name) VALUES (1, ''John'');
INSERT INTO tab1(i, name) VALUES (2, ''Joe'');
INSERT INTO tab1(i, name) VALUES (3, ''Billy'');
--========================================================================--
SELECT /*csv*/ *
FROM tab1
WHERE name = ''jOHN'' ;
-- no rows selected
SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI = ''jOHN'' ;
/*
"I","NAME"
1,"John"
*/
SELECT /*csv*/ *
FROM tab1
WHERE name LIKE ''j%'';
-- no rows selected
SELECT /*csv*/ *
FROM tab1
WHERE name COLLATE BINARY_CI LIKE ''j%'';
/*
"I","NAME"
1,"John"
2,"Joe"
*/
Hay 3 formas principales de realizar una búsqueda que distingue entre mayúsculas y minúsculas en Oracle sin utilizar índices de texto completo.
En última instancia, el método que elija depende de sus circunstancias individuales; Lo más importante que debe recordar es que para mejorar el rendimiento debe indexar correctamente las búsquedas que no distinguen entre mayúsculas y minúsculas.
1. Escribe tu columna y tu cadena de manera idéntica.
Puede forzar que todos sus datos sean el mismo caso utilizando UPPER() o LOWER() :
select * from my_table where upper(column_1) = upper(''my_string'');
o
select * from my_table where lower(column_1) = lower(''my_string'');
Si column_1 no está indexado en upper(column_1) o lower(column_1) , según corresponda, esto puede forzar un escaneo completo de la tabla. Para evitar esto, puede crear un índice basado en funciones .
create index my_index on my_table ( lower(column_1) );
Si está utilizando LIKE, debe concatenar un % alrededor de la cadena que está buscando.
select * from my_table where lower(column_1) LIKE lower(''my_string'') || ''%'';
Este Fiddle de SQL demuestra lo que sucede en todas estas consultas. Tenga en cuenta los planes explicativos, que indican cuándo se está utilizando un índice y cuándo no.
2. Usa expresiones regulares.
A partir de Oracle 10g, REGEXP_LIKE() está disponible. Puede especificar _match_parameter_ ''i'' para realizar una búsqueda que no distinga mayúsculas y minúsculas.
Para utilizar esto como un operador de igualdad, debe especificar el inicio y el final de la cadena, que se denota por quilate y el signo de dólar.
select * from my_table where regexp_like(column_1, ''^my_string$'', ''i'');
Para realizar el equivalente de LIKE, estos pueden ser eliminados.
select * from my_table where regexp_like(column_1, ''my_string'', ''i'');
Tenga cuidado con esto, ya que su cadena puede contener caracteres que serán interpretados de manera diferente por el motor de expresiones regulares.
Este Fiddle de SQL le muestra el mismo resultado de ejemplo, excepto que utiliza REGEXP_LIKE ().
3. Cámbiala a nivel de sesión.
El parámetro NLS_SORT gobierna la secuencia de intercalación para ordenar y los diversos operadores de comparación, incluidos = y LIKE. Puede especificar una clasificación binaria, que no distinga mayúsculas de minúsculas, modificando la sesión. Esto significará que cada consulta realizada en esa sesión realizará parámetros que no distinguen entre mayúsculas y minúsculas.
alter session set nls_sort=BINARY_CI
Hay mucha información adicional sobre la clasificación lingüística y la búsqueda de cadenas si desea especificar un idioma diferente, o hacer una búsqueda sin acento usando BINARY_AI.
También deberá cambiar el parámetro NLS_COMP ; citar:
Los operadores exactos y las cláusulas de consulta que obedecen al parámetro NLS_SORT dependen del valor del parámetro NLS_COMP. Si un operador o cláusula no obedece el valor NLS_SORT, según lo determinado por NLS_COMP, la intercalación utilizada es BINARIA.
El valor predeterminado de NLS_COMP es BINARIO; pero, LINGUISTIC especifica que Oracle debe prestar atención al valor de NLS_SORT:
Las comparaciones para todas las operaciones de SQL en la cláusula WHERE y en los bloques PL / SQL deben usar la clasificación lingüística especificada en el parámetro NLS_SORT. Para mejorar el rendimiento, también puede definir un índice lingüístico en la columna para la que desea comparaciones lingüísticas.
Así que, una vez más, necesitas alterar la sesión.
alter session set nls_comp=LINGUISTIC
Como se indica en la documentación, es posible que desee crear un índice lingüístico para mejorar el rendimiento.
create index my_linguistc_index on my_table
(NLSSORT(column_1, ''NLS_SORT = BINARY_CI''));
puedes hacer algo así:
where regexp_like(name, ''string$'', ''i'');
tal vez puedas intentar usar
SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE ''%ME%''
select user_name
from my_table
where nlssort(user_name, ''NLS_SORT = Latin_CI'') = nlssort(''%AbC%'', ''NLS_SORT = Latin_CI'')