Оператор LIKE cоветы и хитрости программирования
Содержание
Польза предложения LIKE основывается на поддерживаемых им обобщающих операторах. Предложение LIKE возвращает булево значение ИСТИНА, если при сравнении обнаруживаются совпадающие значения.
Для функционирования предложения LIKE очень важна чувствительность к регистру конкретной СУБД. Например, Microsoft SQL Server по умолчанию не учитывает регистр (хотя его можно настроить соответствующим образом). Таким образом, SQL Server будет считать строки DAD и dad одинаковыми. С другой стороны, платформа Oracle учитывает регистр, и строки DAD и dad здесь будут разными. Вот пример, позволяющий лучше проиллюстрировать это положение.
Этот запрос для Microsoft SQL Server извлечет из таблицы authors записи, где фамилия (lname) имеет вид ‘larson’ или ‘lars’, хотя в запросе поиск определен с использованием верхнего регистра (‘LARS%’). В Oracle этот запрос не найдет фамилий ‘Larson’ или ‘Lars’, поскольку Oracle производит сравнение с учетом регистра.
Платформа DB2 поддерживает синтаксис предложения LIKE стандарта ANSI SQL 2003. Поддерживаются обобщающие символы % и знак подчеркивания (_). Поддерживаются отменяющие последовательности.
Платформа DB2 учитывает регистр, поэтому здесь реализация предложения LIKE полностью чувствительна к регистру. Чтобы значения в разных регистрах всегда сравнивались без учета регистра, нужно использовать функцию UPPER или TRANSLATE. Кроме того, DB2 неявно преобразует кодовую страницу шаблона строки или отменяющей последователъности к кодовой странице выражения, если только они не определены с предложением FOR BIT DATA.
MySQL
Платформа MySQL поддерживает для предложения LIKE синтаксис стандарта ANSI. Поддерживаются обобщающие символы % и знак подчеркивания (_). Также поддерживается предложение ESCAPE.
Кроме того, MySQL поддерживает специальные функции REGEXP и NOT RLIKE, применяемые при проверке регулярных выражений. MySQL после версии 3.23.4 по умолчанию не учитывает регистр.
Oracle
Платформа Oracle поддерживает для предложения LIKE синтаксис стандарта ANSI. Поддерживаются обобщающие символы % и знак подчеркивания (_). Также поддерживается предложение ESCAPE. Синтаксис предложения LIKE в Oracle следующий.
LIKE4> строковый_шаблон [ESCAPE отменяющая_последовательность]
Специфичные для Oracle синтаксические элементы имеют следующие значения.
LIKEC
Используется полный набор символов UNICODE.
LIKE2
Используется набор символов UNICODE USC2.
LIKE4
Используется набор символов UNICODE USC4.
Поскольку платформа Oracle учитывает регистр, следует включать выражение, строковый_шаблон или и то и другое в функцию UPPER. В этом случае вы всегда будете сравнивать то, что нужно.
PostgreSQL
Платформа PostgreSQL поддерживает ANSI синтаксис предложения LIKE. Поддерживаются обобщающие символы % и знак подчеркивания (_). Также поддерживаются отменяющие последовательности.
Например, следующие запросы функционально эквивалентны.
Преобразуем значения в верхний регистр
Производим сравнение без учета регистра SELECT * FROM authors WHERE city
Вы должны знать (хотя это и выходит за рамки данной книги), что PostgreSQL также поддерживает регулярные выражения POSIX. Подробности приводятся в описании платформы.
SQL Server
Платформа SQL Server поддерживает ANSI синтаксис предложения LIKE. Поддерживаются отменяющие последовательности. Поддерживаются также следующие дополнительные обобщающие операторы.
Используя дополнительные обобщающие операторы SQL Server, вы получаете дополнительные возможности. Например, вы можете извлечь записи об авторах, фамилии которых Carson, Carsen, Karson или Karsen.
Или вы можете извлечь записи об авторах, фамилии которых заканчиваются на «arson» или «arsen», но не Larsen или Larson.
Дополнительная информация по теме
Некоторые хитрости при использования инструкции REVOKE в базах данных на различных платформах
Некоторые советы использования инструкции MERGE в базах данных на различных платформах
Советы по использованию инструкции UPDATE в базах данных на самых разнообразных платформах
Некоторые особенности использования инструкции INSERT в базах данных на различных платформах
Поиск без учета регистра в Oracle
Поведение по умолчанию для LIKE и других операторов сравнения, = и т. Д. Учитывает регистр.
Можно ли сделать их нечувствительными к регистру?
6 ответов
Начиная с 10gR2, Oracle позволяет точно настраивать поведение сравнения строк, задавая NLS_COMP и NLS_SORT параметры сеанса:
Вы также можете создавать индексы без учета регистра:
Есть 3 основных способа выполнить поиск без учета регистра в Oracle без использования полнотекстовых индексов.
В конечном счете, какой метод вы выберете, зависит от ваших индивидуальных обстоятельств; главное помнить, что для повышения производительности вы должны правильно индексировать поиск без учета регистра.
1. Сохраняйте столбец и строку одинаково.
Вы можете заставить все ваши данные быть одинаковыми, используя UPPER() или LOWER() :
Если вы используете LIKE, вам нужно объединить % вокруг искомой строки.
Этот скрипт SQL демонстрирует, что происходит во всех этих запросах. Обратите внимание на планы объяснения, которые указывают, когда индекс используется, а когда нет.
2. Используйте регулярные выражения.
Чтобы использовать это как оператор равенства, вы должны указать начало и конец строки, которые обозначаются каратом и знаком доллара.
Чтобы выполнить аналог LIKE, их можно удалить.
Будьте осторожны с этим, поскольку ваша строка может содержать символы, которые будут по-разному интерпретироваться обработчиком регулярных выражений.
Этот скрипт SQL показывает тот же пример вывода, за исключением использования REGEXP_LIKE ().
3. Измените его на уровне сеанса.
Параметр NLS_SORT управляет последовательностью сопоставления для упорядочивания. и различные операторы сравнения, включая = и LIKE. Вы можете указать двоичную сортировку без учета регистра, изменив сеанс. Это будет означать, что каждый запрос, выполняемый в этом сеансе, будет выполнять параметры без учета регистра.
Существует много дополнительной информации о лингвистической сортировке и поиске строк если вы хотите указать другой язык, или выполнить поиск без учета акцента, используя BINARY_AI.
Вам также потребуется изменить параметр NLS_COMP. ; Цитировать:
Точные операторы и предложения запроса, которые подчиняются параметру NLS_SORT, зависят от значения параметра NLS_COMP. Если оператор или предложение не подчиняется значению NLS_SORT, как определено NLS_COMP, используется сопоставление BINARY.
При сравнении всех операций SQL в предложении WHERE и в блоках PL / SQL следует использовать лингвистическую сортировку, указанную в параметре NLS_SORT. Чтобы повысить производительность, вы также можете определить лингвистический индекс для столбца, для которого вы хотите лингвистические сравнения.
Итак, еще раз вам нужно изменить сеанс
Как указано в документации, вы можете создать лингвистический индекс для повышения производительности
Регистрозависимый поиск в SQL
SQL запрос, казалось бы, написан абсолютно ясно и прозрачно, но результат не тот, который ожидаем. О маленьких деталях в работе с большими символами пойдёт речь.
В рамках работы над проектом одна из задач заключалась в том, что из огромного массива необходимо было выбрать карты с определённым кодом. Коды карт могут иметь значения как символы типа ‘+’или ‘0’, так и символы — буквы алфавита, в том числе строчные и прописные. При работе с кодами карт в виде цифр и спецсимволов проблем не возникло, НО. В процессе работы с кодами карт в виде символов — букв алфавита разного регистра мы столкнулись с проблемой. Наш простой запрос выдавал нам все данные, независимо от регистра:
Кто и почему не понимает нас?
Оказалось, что на нашем сервере установлены параметры сортировки без учёта регистра.
Я нашла несколько способов решения и хочу поделиться с вами:
Использование функции ASCII, которая возвращает числовой код крайнего левого символа строкового выражения, являющегося аргументом функции.
Например, if ASCII(code)=ASCII(‘m’) is TRUE – значение нашего аргумента строчное;
Мы можем написать запрос сравнения с учетом регистра изменив регистр нашего аргумента и используя collate:
Функция Unicode, которая возвращает юникод первого значения.
Синтаксис функции: unicode ( ).
Возможны варианты использования значений юникодов:
Мы можем создать таблицу- справочник юникодов наших кодов продуктов;
Получим значения юникодов наших аргументов:
либо вычислить юникод изменив регистр нашего аргумента и сравнить с юникодом нашего аргумента;
В результате получим информацию о регистре значения кода для дальнейшего анализа и использования в запросе.
ещё один путь- сразу в коде сравнивать вычисляемое значение с уже известным нам числовым, если их немного и это не трудоёмко.
Именно этот вариант я применила в своей работе, т.к. количество необходимых символов для сравнения было невелико.
Думаю, что предпочтительный вариант каждый определит для себя исходя из специфики задачи и, не исключено, что найдутся другие пути решения, ведь SQL –язык многогранный. Надеюсь, мой опыт будет полезен в работе.
Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite
Базы данных
SQL условие LIKE
В этом учебном материале вы узнаете, как использовать SQL условие LIKE (для сопоставления с шаблоном) с синтаксисом и примерами.
Описание
SQL условие LIKE позволяет использовать подстановочные символы для сопоставления с шаблоном в запросе. Условие LIKE используется в предложении WHERE оператора SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия LIKE в SQL.
Параметры или аргумент
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий SQL оператор.
Будет выбрано 2 записи. Вот результаты, которые вы должны получить.
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 7000 | Tom | Cruise | oracle.com |
| 9000 | Russell | Crowe | google.com |
Использование нескольких подстановочных символов % в условии LIKE
Вы также можете использовать подстановочный символ % несколько раз с условием LIKE.
Используя ту же таблицу customers со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Будет выбрано 3 записей. Вот результаты, которые вы должны получить.
| last_name |
|---|
| Bieber |
| Gomez |
| Depp |
В этом примере фамилии Bieber, Gomez и Depp содержат букву ‘е’.
| category_id | category_name |
|---|---|
| 25 | Deli |
| 50 | Produce |
| 75 | Bakery |
| 100 | General Merchandise |
| 125 | Technology |
Будет выбрано 2 записи. Вот результаты, которые вы должны получить.
| category_id | category_name |
|---|---|
| 25 | Deli |
| 75 | Bakery |
Использование нескольких подстановочных символов _ в условии LIKE
Если вы хотите сопоставить трехзначное значение, заканчивающееся на «5», вам нужно будет использовать подстановочный символ _ два раза. Вы можете изменить свой запрос следующим образом.
Теперь вы вернете значение category_id равное 125.
| category_id | category_name |
|---|---|
| 125 | Technology |
Далее давайте рассмотрим пример использования оператора NOT с условием LIKE.
В этом примере у нас есть таблица suppliers со следующими данными:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Yandex | Moscow | Russian |
| 200 | Lansing | Michigan | |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 700 | Qwant | Paris | France |
| 800 | Menlo Park | California | |
| 900 | Electronic Arts | San Francisco | California |
Будут выбраны 4 записи. Вот результаты, которые вы должны получить.
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Yandex | Moscow | Russian |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 700 | Qwant | Paris | France |
В этом примере в таблице suppliers есть четыре записи, в которых supplier_name не содержит литеру ‘o’.
| test_id | test_value |
|---|---|
| 1 | 10% |
| 2 | 25% |
| 3 | 100 |
| 4 | 99 |
Как я могу искать (без учета регистра) в столбце с использованием шаблона LIKE?
Я огляделся по сторонам и не нашел, что мне нужно, так вот.
Это работает отлично, но не если дерево называется Elm или ELM и т. Д. …
Как сделать SQL-регистр незаметным для этого поиска в wild-card?
Я использую MySQL 5 и Apache.
Фактически, если вы добавите COLLATE UTF8_GENERAL_CI в определение вашего столбца, вы можете просто опустить все эти трюки: он будет работать автоматически.
Это также приведет к восстановлению любых индексов в этом столбце, чтобы они могли использоваться для запросов без указания «%»,
Я всегда решал это, используя ниже:
Чувствительность к регистру определяется в настройках сортировки столбцов / таблиц / баз данных. Вы можете выполнить запрос под определенной сортировкой следующим образом:
Это пример простого запроса LIKE:
Теперь без учета регистра с использованием функции LOWER ():
Я делаю что-то подобное.
Получение значений в нижнем регистре и MySQL делает все остальное
И для MySQL PDO Alternative:
Я думаю, что этот запрос будет делать нечувствительный к регистру поиск:
Просто используйте:
Или использовать
Обе функции работают одинаково
Вы должны настроить правильную кодировку и сопоставление для своих таблиц.
Кодирование таблицы должно отражать фактическое кодирование данных. Какова ваша кодировка данных?
Чтобы увидеть кодировку таблицы, вы можете запустить запрос SHOW CREATE TABLE tablename
Когда я хочу разработать нечувствительные поисковые запросы, я всегда конвертирую каждую строку в нижний регистр, прежде чем делать сравнения
хорошо в mysql 5.5, как и оператор, нечувствителен … поэтому, если ваша долина – это вяза или ELM или Elm или eLM или любой другой, и вы используете «% elm%», он отобразит все соответствующие значения.
Я не могу сказать о более ранних версиях mysql.
Если вы идете в Oracle, например, работайте с учетом регистра, поэтому, если вы наберете «% elm%», он пойдет только для этого и игнорирует верхние регионы.
Странно, но так оно и есть ?
Вам не нужно ALTER любую таблицу. Просто используйте следующие запросы перед запросом SELECT который вы хотите использовать подстановочный знак: