[sql] Ricerca insensibile alle maiuscole e minuscole in Oracle



Answers

Esistono 3 modi principali per eseguire una ricerca senza distinzione tra maiuscole e minuscole in Oracle senza utilizzare indici full-text.

In definitiva, quale metodo scegli dipende dalle circostanze individuali; la cosa principale da ricordare è che per migliorare le prestazioni è necessario indicizzare correttamente per la ricerca senza distinzione tra maiuscole e minuscole.

1. Indica la colonna e la stringa in modo identico.

Puoi forzare tutti i tuoi dati ad essere lo stesso caso usando 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');

Se column_1 non è indicizzato su upper(column_1) o lower(column_1) , come appropriato, questo potrebbe forzare una scansione completa della tabella. Per evitare ciò, è possibile creare un indice basato su funzioni .

create index my_index on my_table ( lower(column_1) );

Se utilizzi LIKE, devi concatenare una % attorno alla stringa che stai cercando.

select * from my_table where lower(column_1) LIKE lower('my_string') || '%';

Questo SQL Fiddle dimostra cosa succede in tutte queste query. Nota i piani esplicativi, che indicano quando viene utilizzato un indice e quando non lo è.

2. Usa le espressioni regolari.

Da Oracle 10g in poi, REGEXP_LIKE() è disponibile. È possibile specificare _match_parameter_ 'i' , per eseguire ricerche senza distinzione tra maiuscole e minuscole.

Per utilizzare questo come operatore di uguaglianza, devi specificare l'inizio e la fine della stringa, che è indicata dal carat e dal simbolo del dollaro.

select * from my_table where regexp_like(column_1, '^my_string$', 'i');

Per poter eseguire l'equivalente di LIKE, questi possono essere rimossi.

select * from my_table where regexp_like(column_1, 'my_string', 'i');

Fai attenzione perché la tua stringa potrebbe contenere caratteri che saranno interpretati in modo diverso dal motore di espressioni regolari.

Questo SQL Fiddle ti mostra lo stesso output di esempio tranne l'uso di REGEXP_LIKE ().

3. Cambiarlo a livello di sessione.

Il parametro NLS_SORT governa la sequenza di confronto per l'ordine e i vari operatori di confronto, inclusi = e LIKE. È possibile specificare un binario, senza distinzione tra maiuscole e minuscole, ordinare alterando la sessione. Ciò significa che ogni query eseguita in quella sessione eseguirà parametri senza distinzione tra maiuscole e minuscole.

alter session set nls_sort=BINARY_CI

Sono disponibili numerose informazioni aggiuntive sull'ordinamento linguistico e sulla ricerca di stringhe se si desidera specificare una lingua diversa o eseguire una ricerca non sensibile all'apprendimento utilizzando BINARY_AI.

Sarà inoltre necessario modificare il parametro NLS_COMP ; per citare:

Gli operatori esatti e le clausole di query che obbediscono al parametro NLS_SORT dipendono dal valore del parametro NLS_COMP. Se un operatore o una clausola non obbedisce al valore NLS_SORT, come determinato da NLS_COMP, la collation utilizzata è BINARY.

Il valore predefinito di NLS_COMP è BINARY; ma, LINGUISTIC specifica che Oracle dovrebbe prestare attenzione al valore di NLS_SORT:

I confronti per tutte le operazioni SQL nella clausola WHERE e nei blocchi PL / SQL devono utilizzare l'ordinamento linguistico specificato nel parametro NLS_SORT. Per migliorare le prestazioni, è inoltre possibile definire un indice linguistico sulla colonna per il quale si desidera il confronto linguistico.

Quindi, ancora una volta, è necessario modificare la sessione

alter session set nls_comp=LINGUISTIC

Come indicato nella documentazione, è possibile creare un indice linguistico per migliorare le prestazioni

create index my_linguistc_index on my_table 
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));
Question

Il comportamento predefinito di LIKE e degli altri operatori di confronto, = etc è case-sensitive.

È possibile renderli insensibili alle maiuscole e alle minuscole?




select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')



Da Oracle 12c R2 è possibile utilizzare l' COLLATE operator :

L'operatore COLLATE determina le regole di confronto per un'espressione. Questo operatore consente di sovrascrivere le regole di confronto che il database avrebbe derivato per l'espressione utilizzando regole di derivazione di regole di confronto standard.

L'operatore COLLATE accetta un argomento, nome_collazione, per il quale è possibile specificare una collazione denominata o una pseudo-collazione. Se il nome della collazione contiene uno spazio, è necessario racchiudere il nome tra virgolette doppie.

demo:

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"
*/





Related