Поиск без учета регистра в Oracle

187

Поведение по умолчанию LIKE и других операторов сравнения = т.д. Учитывает регистр.

Возможно ли сделать их нечувствительными к регистру?

  • 0
    Дружеское напоминание о том, что некоторые примеры поиска приведут к полному сканированию таблицы, даже если есть индекс для user_name.
  • 5
    Рассматривали ли вы использовать REGEXP_LIKE(username,'me','i') вместо LIKE?
Показать ещё 1 комментарий
Теги:
case-sensitive
case-insensitive
sql-like

6 ответов

68
Лучший ответ

Поскольку 10gR2, Oracle позволяет точно настроить поведение сравнения строк, установив NLS_COMP и 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

Вы также можете создавать индексы без регистров:

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

Эта информация была взята из нечувствительных к регистру запросов в Oracle. В статье упоминается REGEXP_LIKE, но, похоже, он работает и с добрым старым =.


В версиях старше 10gR2 это не может быть действительно сделано, и обычный подход, если вам не нужен поиск с акцентовым доступом, - это просто UPPER() как столбец, так и выражение поиска.

  • 1
    Это хорошо работает, но делает ОБНОВЛЕНИЯ с использованием операторов LIKE / = очень медленными ...... :(
  • 1
    @SaqibAli Произвольные выражения LIKE (например, WHERE foo LIKE '%abc%' ) уже достаточно медленные, если не могут быть проиндексированы, я не думаю, что это определенно связано с чувствительностью к регистру.
Показать ещё 3 комментария
251

Существует 3 основных способа выполнить поиск без учета регистра в Oracle без использования полнотекстовых индексов.

В конечном счете, какой метод вы выбираете, зависит от ваших индивидуальных обстоятельств; главное, чтобы помнить, что для повышения производительности вы должны правильно индексировать регистр без учета регистра.

1. Введите столбец и строку точно.

Вы можете заставить все свои данные быть в одном и том же случае, используя UPPER() или LOWER():

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

или

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

Если column_1 не индексируется на upper(column_1) или lower(column_1), это может привести к полному сканированию таблицы. Чтобы этого избежать, вы можете создать функциональный индекс.

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

Если вы используете LIKE, вам необходимо объединить % вокруг строки, которую вы ищете.

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

Этот скрипт SQL показывает, что происходит во всех этих запросах. Обратите внимание на Объяснительные планы, которые указывают, когда используется индекс, и когда это не так.

2. Используйте регулярные выражения.

От Oracle 10g вперед REGEXP_LIKE() доступен. Вы можете указать _match_parameter_ 'i', чтобы выполнить поиск без учета регистра.

Чтобы использовать это как оператор равенства, вы должны указать начало и конец строки, которая обозначается значком карата и знака доллара.

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

Чтобы выполнить эквивалент LIKE, они могут быть удалены.

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

Будьте осторожны с этим, так как ваша строка может содержать символы, которые будут интерпретироваться по-разному с помощью механизма регулярных выражений.

Этот скрипт SQL показывает вам тот же примерный вывод, за исключением использования REGEXP_LIKE().

3. Измените его на уровне сеанса.

Параметр NLS_SORT управляет последовательностью сортировки для упорядочения и различными операторами сравнения, включая = и LIKE. Вы можете указать двоичный, нечувствительный к регистру, сортировку, изменив сеанс. Это будет означать, что каждый запрос, выполняемый в этом сеансе, будет выполнять нечувствительные к регистру параметры.

alter session set nls_sort=BINARY_CI

Имеется много дополнительной информации о лингвистической сортировке и поиске строк, если вы хотите указать другой язык или выполнить поиск без акцента, используя BINARY_AI.

Вам также потребуется изменить параметр NLS_COMP; цитата:

Точные операторы и предложения запроса, которые подчиняются параметру NLS_SORT зависят от значения параметра NLS_COMP. Если оператор или не соответствует значению NLS_SORT, как определено NLS_COMP, используемая сортировка - BINARY.

Значение по умолчанию NLS_COMP равно BINARY; но, LINGUISTIC указывает, что Oracle следует обратить внимание на значение NLS_SORT:

Сравнение всех операций SQL в предложении WHERE и в PL/SQL блоки должны использовать лингвистическую сортировку, указанную в NLS_SORT параметр. Чтобы повысить производительность, вы также можете определить лингвистический индекс в столбце, для которого вы хотите лингвистическую сравнения.

Итак, еще раз вам нужно изменить сеанс

alter session set nls_comp=LINGUISTIC

Как указано в документации, вы можете создать лингвистический индекс для повышения производительности

create index my_linguistc_index on my_table 
   (NLSSORT(column_1, 'NLS_SORT = BINARY_CI'));
  • 0
    "создать индекс на основе функций" Удивительно, как это может измениться
  • 0
    Могу ли я спросить, почему по-другому select * from my_table where lower(column_1) LIKE lower('my_string') || '%'; вместо select * from my_table where lower(column_1) LIKE lower('my_string%'); ? Это дает какое-то преимущество?
Показать ещё 6 комментариев
38

возможно, вы можете попробовать использовать

SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'
  • 0
    пробовал уже, не работает
  • 0
    Почему это не работает? Кажется, хорошо для меня.
Показать ещё 13 комментариев
2

Из Oracle 12c R2 вы можете использовать COLLATE operator:

Оператор COLLATE определяет сортировку для выражения. Этот оператор позволяет вам переопределить сопоставление, которое база данных могла бы вывести для выражения, используя стандартные правила деривации сортировки.

Оператор COLLATE принимает один аргумент, имя_сображения, для которого вы можете указать именованное сопоставление или псевдосображение. Если имя сортировки содержит пробел, вы должны заключить это имя в двойные кавычки.

Демо-версия:

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

вы можете сделать что-то вроде этого:

where regexp_like(name, 'string$', 'i');
1
select user_name
from my_table
where nlssort(user_name, 'NLS_SORT = Latin_CI') = nlssort('%AbC%', 'NLS_SORT = Latin_CI')
  • 0
    % В первом аргументе вашего второго NLSSORT не предназначены для подстановочных знаков, верно? Они отчасти смущают.

Ещё вопросы

Сообщество Overcoder
Наверх
Меню