Wyszukiwanie bez rozróżniania wielkości liter w Oracle

W domyślnym zachowaniu LIKEi innych operatorach porównania =itp. Rozróżniana jest wielkość liter.

Czy to możliwe, aby rozróżniała wielkość liter?

Od wersji 10gR2 Oracle umożliwia precyzyjne dostosowanie zachowania porównań ciągów poprzez ustawienie parametrów NLS_COMPi NLS_SORTsesji:

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

Możesz także tworzyć indeksy bez rozróżniania wielkości liter:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

Informacje te pochodzą z wyszukiwań Oracle bez rozróżniania wielkości liter . Artykuł wspomina, REGEXP_LIKEale wydaje się, że działa również ze starym =dobrym.

W wersjach starszych niż 10gR2 tak naprawdę nie można tego zrobić, a zwykłe podejście, jeśli nie potrzebujesz wyszukiwania niewrażliwego na akcent , dotyczy tylko UPPER()kolumny i wyrażenia wyszukiwania.

Istnieją 3 główne sposoby przeprowadzania wyszukiwania Oracle bez rozróżniania wielkości liter bez użycia indeksów pełnotekstowych.

Ostatecznie wybór metody zależy od indywidualnych okoliczności; Najważniejszą rzeczą do zapamiętania jest to, że aby poprawić wydajność, musisz poprawnie indeksować wyszukiwanie bez rozróżniania wielkości liter.

1. Spraw, by kolumna i ciąg znaków były identyczne.

Możesz wymusić, aby wszystkie dane były identyczne, używając UPPER()lub LOWER():

select * from my_table where upper(column_1) = upper('my_string');

lub

select * from my_table where lower(column_1) = lower('my_string');

Jeśli column_1nie jest zaindeksowany upper(column_1)lub lower(column_1), w stosownych przypadkach, może to wymusić pełne skanowanie tabeli. Aby tego uniknąć, możesz utworzyć indeks oparty na funkcjach .

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

Jeśli używasz LIKE, musisz łączyć ciąg %wokół szukanego ciągu.

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

Ten SQL Fiddle pokazuje, co dzieje się we wszystkich tych zapytaniach. Zwróć uwagę na wyjaśnienia, które wskazują, kiedy indeks jest używany, a kiedy nie.

2. Użyj wyrażeń regularnych.

Od Oracle 10g REGEXP_LIKE()jest dostępna. Możesz określić _match_parameter_ 'i', aby przeprowadzić wyszukiwanie bez rozróżniania wielkości liter.

Aby użyć tego jako operatora równości, musisz określić początek i koniec łańcucha, który jest oznaczony przez karat i znak dolara.

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

Aby wykonać odpowiednik LIKE, można je usunąć.

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

Uważaj na to, ponieważ łańcuch może zawierać znaki, które będą interpretowane w różny sposób przez silnik wyrażeń regularnych.

To skrzypce SQL pokazuje te same przykładowe dane wyjściowe, z wyjątkiem użycia REGEXP_LIKE ().

3. Zmień to na poziomie sesji.

NLS_SORT parametr reguluje kolejność sortowania dla zamawiającego i różnych operatorów porównania, w tym =i podobnych. Możesz określić binarne sortowanie bez rozróżniania wielkości liter, zmieniając sesję. Oznacza to, że każde zapytanie wykonane w tej sesji wykona parametry bez rozróżniania wielkości liter.

alter session set nls_sort=BINARY_CI

Istnieje wiele dodatkowych informacji na temat sortowania językowego i wyszukiwania ciągów, jeśli chcesz określić inny język lub przeprowadzić wyszukiwanie niewrażliwe na akcent przy użyciu BINARY_AI.

Będziesz także musiał zmienić parametr NLS_COMP ; cytować:

Dokładne operatory i klauzule zapytania, które są zgodne z parametrem NLS_SORT, zależą od wartości parametru NLS_COMP. Jeśli operator lub klauzula nie przestrzega wartości NLS_SORT, określonej przez NLS_COMP, zastosowane sortowanie jest BINARNE.

Domyślna wartość NLS_COMP to BINARY; ale LINGUISTIC określa, że ​​Oracle powinno zwracać uwagę na wartość NLS_SORT:

Porównania wszystkich operacji SQL w klauzuli WHERE i blokach PL / SQL powinny używać sortowania językowego określonego w parametrze NLS_SORT. Aby poprawić wydajność, możesz również zdefiniować indeks językowy w kolumnie, dla której chcesz porównać lingwistyczne.

Więc jeszcze raz musisz zmienić sesję

alter session set nls_comp=LINGUISTIC

Jak zauważono w dokumentacji, możesz chcieć utworzyć indeks językowy w celu poprawy wydajności

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

może możesz spróbować użyć

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'

Z Oracle 12c R2 możesz użyć COLLATE operator:

Operator COLLATE określa sortowanie wyrażenia. Ten operator umożliwia zastąpienie sortowania, które baza danych wyprowadziłaby dla wyrażenia, przy użyciu standardowych reguł wyprowadzania sortowania.

Operator COLLATE pobiera jeden argument, nazwa_kolacji, dla którego można określić nazwane sortowanie lub pseudokolację. Jeśli nazwa sortowania zawiera spację, należy ją zawrzeć w podwójnych cudzysłowach.

Próbny:

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

db <> demo skrzypiec

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

możesz zrobić coś takiego:

where regexp_like(name, 'string$', 'i');