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

6 ответов

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

  ответ дан Ben, с репутацией 39989, 9.02.2013
• 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')
     )
  ;
  

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

  ---

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

  ответ дан Álvaro González, с репутацией 102408, 22.03.2011
• 40 рейтинг

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

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

  ответ дан V4Vendetta, с репутацией 27669, 22.03.2011
• 2 рейтинг

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

  ответ дан Lukasz Szozda, с репутацией 72255, 15.02.2018
• 1 рейтинг

  вы можете сделать что-то подобное:

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

  ответ дан grep, с репутацией 2031, 19.11.2014
• 1 рейтинг

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

  ответ дан Clodoaldo Neto, с репутацией 69616, 22.03.2011