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


228

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?


Przyjazne przypomnienie, że niektóre przykładowe wyszukiwania spowodują pełne skanowanie tabeli, nawet jeśli istnieje nazwa użytkownika.
JonSG

8
Czy rozważałeś użycie REGEXP_LIKE(username,'me','i')zamiast LIKE?
kubańczyk

5
nie, LIKE działa dla mnie dobrze
sergionni

Odpowiedzi:


82

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.


1
Działa to dobrze, ale powoduje, że AKTUALIZACJE przy użyciu operatorów LIKE / = są bardzo wolne ...... :(
Saqib Ali

1
@ SaqibAli Arbitralne LIKEwyrażenia (np. WHERE foo LIKE '%abc%') Są już wystarczająco wolne, jeśli nie można ich zindeksować, nie sądzę, żeby miało to związek z rozróżnianiem wielkości liter.
Álvaro González

1
Możesz także ustawić je poza SQLPLUS, tak jak w środowisku powłoki. Na przykład używając skryptu Perla DBD::Oracle, możesz napisać $ENV{NLS_SORT} = 'BINARY_CI'; $ENV{NLS_COMP} = 'LINGUISTIC';przed wywołaniem `DBI-> connect`.
mivk

hej, ALTER SESSIONjedyna zmiana zmienia lokalną instancję korekty i oznacza to, że twoja bieżąca sesja to znaczy, że jeśli zamknę i ponownie otworzę, zresetuje się. Czy jest sposób, w jaki mogę zobaczyć, jakie są obecne wartości, więc jeśli
utrzyma się

305

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'));

„Utwórz indeks oparty na funkcjach” Niesamowite, jaką to może zmienić
Jacob Goulden

Mogę zapytać, dlaczego jest inaczej zrobić select * from my_table where lower(column_1) LIKE lower('my_string') || '%';zamiast select * from my_table where lower(column_1) LIKE lower('my_string%');? Czy daje to jakąś przewagę?
lopezvit,

1
Jednym z powodów może być sparamerizowanie zapytania (prawdopodobnie w większości sytuacji), a następnie kod wywołujący nie musi zawsze konkatenować% na końcu @lopezvit.
Ben

1
Jeśli są jakieś postacie, które zepsują wynik regexp_like, czy istnieje sposób na uniknięcie takich ciągów? Podając przykład, jeśli ciąg ma $, wynik nie będzie zgodny z oczekiwaniami. // cc @Ben i inni, proszę, udostępnij.
bozzmob

2
` jest znakiem ucieczki @bozzmob. Nie powinno być różnicy w danych wyjściowych, jeśli ciąg, na którym działa wyrażenie regularne, zawiera a $, może to powodować problemy tylko wtedy, gdy potrzebujesz $literału w wyrażeniu regularnym. Jeśli masz konkretny problem, zadam inne pytanie, czy ten komentarz / odpowiedź nie pomogła.
Ben

51

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

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

3
To działa, gdy parametr wejściowy jest całe dużymi literami, a jeżeli jest ona niższa lub mieszany to nie
sergionni

13
Czy myślałeś WHERE upper(user_name) LIKE UPPER('%ME%')wtedy? :)
Konerak

3
@sergionni musisz również wpisać szukaną frazę!
Markus Winand

3
@sergionni, więc dlaczego nie użyjesz UPPERteż parametru wejściowego?
Czechnology

5
@ V4Vendetta za pomocą upperfunkcji tracisz indeks, czy masz pomysł, jak wyszukiwać za pomocą indeksu?
jcho360

7

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


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

W %„s w pierwszym argumencie do drugiego NLSSORTnie miało być symbole wieloznaczne, prawda? Trochę mylą.
Stefan van den Akker

1

możesz zrobić coś takiego:

where regexp_like(name, 'string$', 'i');
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.