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

существует 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) );

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

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. Измените его на уровне сеанса.

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

alter session set nls_sort=BINARY_CI

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

Вам также нужно будет изменить NLS_COMP; цитата:

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

значение по умолчанию NLS_COMP является двоичным; но, 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'));

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

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

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

оператор COLLATE определяет параметры сортировки для выражения. Этот оператор позволяет переопределить параметры сортировки, полученные базой данных для выражения с помощью стандартных правил вывода параметров сортировки.

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

демо:

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

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

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

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