Поведение по умолчанию для LIKE
и других операторов сравнения, =
и т. Д., Чувствительно к регистру.
Можно ли сделать их нечувствительными к регистру?
Поведение по умолчанию для LIKE
и других операторов сравнения, =
и т. Д., Чувствительно к регистру.
Можно ли сделать их нечувствительными к регистру?
Начиная с версии 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()
и столбец, и выражение поиска.
LIKE
выражения (например, WHERE foo LIKE '%abc%'
) уже достаточно медленные, если их нельзя проиндексировать, я не думаю, что это конкретно связано с чувствительностью к регистру.
- person Álvaro González; 30.07.2015
DBD::Oracle
, вы можете написать $ENV{NLS_SORT} = 'BINARY_CI'; $ENV{NLS_COMP} = 'LINGUISTIC';
перед вызовом DBI- ›connect.
- person mivk; 08.02.2017
ALTER SESSION
только изменяет ваш локальный экземпляр исправления и означает ли это, как ваш текущий сеанс, т.е. если я закрою и снова открою, он будет сброшен. Есть ли способ увидеть текущие значения, чтобы, если они сохраняются повсюду, я мог вернуться к исходным настройкам ...
- person Seabizkit; 13.06.2017
Есть 3 основных способа выполнить поиск без учета регистра в Oracle без использования полнотекстовых индексов.
В конечном счете, какой метод вы выберете, зависит от ваших индивидуальных обстоятельств; главное помнить, что для повышения производительности вы должны правильно индексировать поиск без учета регистра.
Вы можете заставить все свои данные быть в одном регистре, используя UPPER()
или LOWER()
:
select * from my_table where upper(column_1) = upper('my_string');
or
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 демонстрирует, что происходит во всех этих запросах. Обратите внимание на планы объяснения, которые указывают, когда индекс используется, а когда нет.
Начиная с 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 ().
Параметр 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'));
select * from my_table where lower(column_1) LIKE lower('my_string') || '%';
вместо select * from my_table where lower(column_1) LIKE lower('my_string%');
? Дает ли это преимущество?
- person lopezvit; 25.01.2016
regexp_like
, есть ли способ избежать таких строк? Приведем пример: если в строке есть $, результат будет не таким, как мы ожидали. // cc @Ben и другие, пожалуйста, поделитесь.
- person bozzmob; 11.07.2016
`
- escape-символ @bozzmob. Не должно быть никакой разницы в выводе, если строка, с которой работает регулярное выражение, содержит $
, это может вызвать проблемы только в том случае, если вам нужен литерал $
в регулярном выражении. Если у вас есть конкретная проблема, я задам другой вопрос, если этот комментарий / ответ не помог.
- person Ben; 11.07.2016
UPPER()
или LOWER()
.
- person Ben; 02.10.2019
может ты можешь попробовать использовать
SELECT user_name
FROM user_master
WHERE upper(user_name) LIKE '%ME%'
WHERE upper(user_name) LIKE UPPER('%ME%')
? :)
- person Konerak; 22.03.2011
UPPER
и во входном параметре?
- person Czechnology; 22.03.2011
%
. Таким образом, index не будет использоваться.
- person zerkms; 22.03.2011
%ME%
, вероятно, происходит откуда-то из переменной, но автор об этом не упомянул;) Вы правы, если %ME%
буквально такое же, как это в предложении where, UPPER(%ME%) == %ME%
, поэтому нет никакой разницы.
- person Konerak; 22.03.2011
upper
, вы теряете индекс, знаете ли вы, как выполнять поиск по индексу?
- person jcho360; 28.02.2013
Из Oracle 12c R2 вы можете использовать _ 1_:
Оператор 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"
*/
вы можете сделать что-то вроде этого:
where regexp_like(name, 'string$', 'i');
%
в первом аргументе вашего второго NLSSORT
не предназначены для использования подстановочных знаков, верно? Они как бы сбивают с толку.
- person Stefan van den Akker; 04.10.2016
REGEXP_LIKE(username,'me','i')
вместо LIKE? - person kubanczyk   schedule 13.02.2012