LIKE и REGEXP _LIKE

Операторы LIKE и REGEXP_LIKE в запросах Oracle SQL, символы % и _, регулярные выражения в запросах

Очень часто в практической работе возникает необходимость провести поиск по набору символов в любом месте столбца - например, для поиска всех записей с названиями товаров, содержащими какое-либо слово, или для поиска какого-либо слова как в единственном, так и во множественном числе. Для этой цели в Oracle SQL можно использовать оператор LIKE (его можно использовать и в SQL Server ) и оператор REGEXP _LIKE (этого оператора в SQL Server не предусмотрено).

Сразу скажем, что LIKE и REGEXP _LIKE используются только в относительно простых ситуациях. Если вам нужно производить поиск с учетом грамматики, фильтрации шумовых слов и т.п., то следует подумать о применении полнотекстового поиска.

Оператор LIKE используется для поиска символьных значений по шаблону со специальными подстановочными символами (метасимволами). Отметим, что в Oracle предусмотрено четыре разновидности этого оператора:

· "обычный" LIKE - применяется для запроса к строковым столбцам с традиционными кодировками;

· LIKEC - применяется для столбцов с кодировкой Unicode (в терминологии Oracle - Unicode complete );

· LIKE2 - для кодировки UCS2 ;

· LIKE4 - для кодировки UCS4 .

Пример применения этого оператора может выглядеть так:

like "R%";

В этом примере last _name после ключевого слова where - это столбец, из которого берутся строковые значения для проверки, а "R %"- это условие для проверки. Отличительной особенностью оператора LIKE является то, что он может включать в условие специальные подстановочные символы (метасимволы). Для этого оператора предусмотрено только два подстановочных символа:

• % - представляет любую последовательность из нуля или более символов. При этом значение "%" никогда не будет равно NULL (для проверки таких значений используется IS NULL );
• _ - представляет любой одиночный символ.

В нашем примере мы использовали подстановочный символ %, поэтому запрос вернет всех сотрудников, чья фамилия начинается на R .

Если мы хотим, чтобы символ процента (%) и подчеркивания (_) воспринимались Oracle как обычные символы, по которым можно производить поиск (а не как служебгные подстановочные символы), в нашем распоряжении - параметр ESCAPE . При помощи этого параметра можно определить специальный символ, после которого подстановочный символ будет считаться обычным:

select last_name from hr.employees where last_name like "R\%" ESCAPE "\";

В нашем примере мы определили символ \ в качестве отмены подстановочного значения, и если у нас найдется сотрудник с фамилией R %, то запрос его обязательно вернет. Чаще всего для символа отмены подстановочного значения используется обратный слэш (\) - наиболее привычное значение для программистов на C , однако вы вполне можете использовать любой символ, определив его при помощи ESCAPE .

Условие LIKE использовать совсем несложно, но и набор возможностей у него очень ограничен. Намного большие функциональные возможности предоставляет условие REGEXP _LIKE , которое позволяет определять условие поиска при помощи стандартных POSIX -совместимых регулярных выражений. Регулярные выражения - очень большая тема, которая выходит за рамки этого учебного курса. Полную справку по регулярным выражениям, поддерживаемым Oracle , можно посмотреть в приложении C книги SQL Reference документации Oracle . Здесь же мы приведем только простой пример:

SELECT first _ name FROM employees

WHERE REGEXP_LIKE (first_name, "^Ste(v|ph)en$");

В этом примере мы возвращаем информацию о всех сотрудниках, у которых имя начинается на Ste , заканчивается на en , а между ними находится либо v , либо ph . Результат может быть таким:

Steven

Steven

Польза предложения LIKE основывается на поддерживаемых им обобщающих операторах. Предложение LIKE возвращает булево значение ИСТИНА, если при сравнении обнаруживаются совпадающие значения.

Для функционирования предложения LIKE очень важна чувствительность к регистру конкретной СУБД. Например, Microsoft SQL Server по умолчанию не учитывает регистр (хотя его можно настроить соответствующим образом). Таким образом, SQL Server будет считать строки DAD и dad одинаковыми. С другой стороны, платформа Oracle учитывает регистр, и строки DAD и dad здесь будут разными. Вот пример, позволяющий лучше проиллюстрировать это положение.

SELECT * FROM authors WHERE lname LIKE "LARS%"

Этот запрос для Microsoft SQL Server извлечет из таблицы authors записи, где фамилия (lname) имеет вид "larson" или "lars", хотя в запросе поиск определен с использованием верхнего регистра ("LARS%"). В Oracle этот запрос не найдет фамилий "Larson" или "Lars", поскольку Oracle производит сравнение с учетом регистра.

DB2

Платформа 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 следующий.

WHERE выражение {LIKE | LIKEC | LIKE2 |

LIKE4} строковый_шаблон

Специфичные для Oracle синтаксические элементы имеют следующие значения.

Используется полный набор символов UNICODE.

Используется набор символов UNICODE USC2.

Используется набор символов UNICODE USC4.

Поскольку платформа Oracle учитывает регистр, следует включать выражение, строковый_шаблон или и то и другое в функцию UPPER. В этом случае вы всегда будете сравнивать то, что нужно.

PostgreSQL

Платформа PostgreSQL поддерживает ANSI синтаксис предложения LIKE. Поддерживаются обобщающие символы % и знак подчеркивания (_). Также поддерживаются отменяющие последовательности.

PostgreSQL по умолчанию учитывает регистр. Для сравнения без учета регистра в PostgreSQL существует ключевое слово ILIKE. Также вы можете использовать оператор - как эквивалент LIKE и -* как эквивалент ILIKE, а также!- и!-* как эквиваленты NOT LIKE и NOT ILIKE соответственно. Это все расширения стандарта ANSI, которые существуют в PostgreSQL.

Например, следующие запросы функционально эквивалентны.

SELECT * FROM authors WHERE city LIKE "%ville"; SELECT * FROM authors WHERE city -- ^ville";

Поскольку в этих примерах используется нижний регистр, вы можете столкнуться с проблемой учета регистра. То есть запрос ведет поиск по строке "%ville" в нижнем регистре, а таблица может содержать значения в верхнем регистре, которые не попадут в результаты - "BROWNSVILLE", "NASHVILLE", "HUNTSVILLE". Эту проблему можно решить так, как показано в следующем примере.

Преобразуем значения в верхний регистр

SELECT * FROM authors WHERE city LIKE UPPER("%ville");

Производим сравнение без учета регистра SELECT * FROM authors WHERE city ~~* "%ville";

SELECT * FROM authors WHERE city LIKE "%ville";

Вы должны знать (хотя это и выходит за рамки данной книги), что PostgreSQL также поддерживает регулярные выражения POSIX. Подробности приводятся в описании платформы.

SQL Server

Платформа SQL Server поддерживает ANSI синтаксис предложения LIKE. Поддерживаются отменяющие последовательности. Поддерживаются также следующие дополнительные обобщающие операторы.

Соответствует любому значению из указанного набора, например , или диапазона, например [к-п].

[Л] - соответствует любому символу, не входящему в указанный набор или диапазон.

Используя дополнительные обобщающие операторы SQL Server, вы получаете дополнительные возможности. Например, вы можете извлечь записи об авторах, фамилии которых Carson, Carsen, Karson или Karsen.

SELECT * FROM authors WHERE au_lname LIKE "arsn"

SELECT * FROM authors WHERE au_lname LIKE "arsn"

Тема 3.2. Выборка данных с использованием предложения SELECT

Все SQL-выражения, предназначенные для выборки данных из существующих таблиц БД, начинаются с ключевого слова (оператора) SELECT (выбрать). Для уточнения запроса служат дополнительные операторы, такие как FROM (из), WHERE (где) и др.

Простейший синтаксис SELECT запроса:

SELECT <список столбцов>

FROM <список таблиц>

В указаны операторы, которые могут отсутствовать в запросе. Данные операторы используются для уточнения запроса на выборку данных:

p WHERE (где) – указывает записи, которые должны войти в результирующую таблицу (фильтр записей);

p GROUP BY (группировать по) – группирует записи по значениям определенных столбцов;

p HAVING (имеющие, при условии) – указывает группы записей, которые должны войти в результирующую таблицу (фильтр групп);

p ORDER BY (сортировать по) – сортирует (упорядочивает) записи.

Операторы SELECT и FROM являются обязательными. Ключевое слово SELECT сообщает БД, что данное предложение является запросом на извлечение информации. После слова SELECT через "," перечисляются наименования столбцов, содержание которых запрашивается. После слова FROM указывается список имен таблиц (через ","), из которых извлекается информация.

Пример:

SELECT NAME, SURNAME

Приведенный запрос осуществляет выборку всех значений NAME и SURNAME из таблицы STUDENTS. Результатом является таблица, состоящая из 2-х столбцов.

Порядок следования столбцов этой таблицы соответствует порядку полей, указанному в запросе, а не их порядку в таблице STUDENTS.

Если необходимо получить все столбцы таблицы, то вместо списка столбцов достаточно указать символ (*) .

Пример:

SELECT *

Сразу за оператором SELECT до списка столбцов можно применять ключевые слова ALL (все) и DISTINCT (отличающиеся) , которые указывают, какие записи представлять в результирующей таблице. Если эти ключевые слова не используются, то подразумевается, что следует выбрать все записи (что также соответствует применению ключевого слова ALL). В случае использования DISTINCT в результирующей таблице представляются только уникальные записи. При этом если в исходной таблице находятся несколько идентичных записей, то из них выбирается только первая.

Пример:

SELECT DISTINCT CITY

Если в выражении FROM указано несколько таблиц, то в выражении SELECT имена столбцов должны содержать префиксы , указывающие, к какой именно таблице они относятся. Префикс отделяется от имени столбца точкой.

Пример:

Выражение STUDENTS.NAME означает столбец NAME из таблицы STUDENTS

Заголовки столбцов в результирующей таблице можно переопределить по своему усмотрению, назначив для них так называемые псевдонимы . Для этого в списке столбцов после соответствующего столбца следует написать выражение вида: AS заголовок-столбца

Пример:

SELECT NAME AS Имя, SURNAME AS Фамилия

Псевдонимы также можно задать и для каждой таблицы после ключевого слова FROM. Для этого достаточно указать псевдоним через пробел сразу после имени соответствующей таблицы. Псевдонимы таблиц, более короткие, чем их имена, удобно использовать в сложных запросах.

Пример:

SELECT T1.NAME , T1.SURNAME, T2.SUM_STIPEND

FROM STUDENTS T1, STIPEND T2;

Оператор WHERE

Условия поиска в операторе WHERE являются логическими выражениями, т.е. принимают одно из трех возможных значений: true , false и NULL (это происходит, когда в выражении некоторые элементы имеют значение NULL) . Таким образом, в SQL мы имеем дело с трехзначной логикой.

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

ü предикаты сравнения: (=), (<), (>), (<>), (<=), (>=);

ü LIKE, NOT LIKE;

ü ALL, SOME, ANY;

Пример:

WHERE SURNAME = "Петров";

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

Пример:

Написать запрос на получение имен и фамилий студентов, обучающихся на 3 курсе и получающих стипендию:

SELECT NAME, SURNAME

WHERE KURS=3 AND STIPEND>0;

Оператор BETWEEN

Предикат BETWEEN (между) позволяет задать выражение проверки вхождения какого-либо значения в диапазон, определяемый граничными значениями.

Пример:

SELECT SUBJECT_NAME

WHERE HOURS BETWEEN 30 AND 40;

Вывести названия предметов, на изучение которых отводится количество часов в пределах от 30 до 40.

Граничные значения входят в диапазон значений, с которыми производится сравнение.

Эквивалентным приведенному является выражение с предикатами сравнения:

SELECT SUBJECT_NAME

WHERE HOURS>30 AND HOURS<40;

Кроме данных числового типа, в выражениях с BETWEEN можно использовать данные следующих типов: символьные, битовые, даты-времени.

Операторы IN и NOT IN

Предикаты IN (в) и NOT IN (не в) применяются для проверки вхождения какого-либо значения в заданный список значений.

Построенный с использованием IN предикат считается истинным, если значение поля, имя которого указывается слева от IN совпадает с одним из значений в списке.

Пример:

SELECT STUDENT_ID

WHERE MARK IN (4, 5);

Получить из таблицы EXAM_MARKS сведения о студентах, имеющих экзаменационные оценки только 4 и 5.

NOT IN – не совпадает ни с одним из значений

Пример:

SELECT STUDENT_ID

WHERE MARK NOT IN (0, 1, 2, 3);

Получить из таблицы EXAM_MARKS сведения о студентах, не имеющих неудовлетворительные экзаменационные оценки.

Операторы LIKE и NOT LIKE

Предикаты LIKE (похожий) и NOT LIKE (не похожий) применяются для проверки частичного соответствия символьных строк. Этот оператор просматривает строковые значения полей с целью определения: входит ли заданная в оператор LIKE строка в символьную строку_значение проверяемого поля.

Критерий частичного соответствия задается с помощью двух символов-масок: знака процента (%) и подчеркивания (_). Знак процента (%) означает любой набор символов, в том числе и пустой, а символ подчеркивания (_) – любой одиночный символ.

Пример:

SELECT *

WHERE SURNAME LIKE "Р%";

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

Если необходимо исключить всех студентов, по фамилии "Петров", необходимо выполнить следующий запрос:

SELECT *

WHERE SURNAME NOT LIKE "Петров";

В случае необходимости включения в "образец" самих символов подчеркивания или процента, необходимо, чтобы такие символы воспринимались интерпретатором SQL не как символы-маски. Чтобы знак процента или подчеркивания воспринимался буквально, перед ним необходимо указать специальный управляющий символ. Этот символ можно определить произвольно, лишь бы он не встречался в качестве элемента данных.

Пример:

SELECT Имя, Адрес, Процент_скидки

FROM Клиенты

WHERE Процент_скидки LIKE "20#%"

Здесь за ключевым словом ESCAPE указывается символ, который используется в качестве управляющего. Таким же способом можно отключить и сам управляющий символ.

Пример:

LIKE "_\ _P"

В данном выражении символ "\" с помощью ключевого слова ESCAPE объявляется ESC-символом. 1-ый символ "_" будет соответствовать как и ранее любому символу в проверяемой строке, а 2-ой символ "_" будет интерпретироваться буквально, как обычный символ подчеркивания.

Оператор IS NULL

Предикат IS NULL применяется для выявления записей, в которых тот или иной столбец не имеет значения.

Пример:

Получить записи о клиентах, для которых не указан адрес возможно с помощью следующего запроса:

SELECT Имя, Адрес, Регион

FROM Клиенты

WHERE Адрес IS NULL;

Для получения записей, в которых столбец "Адрес" содержит некоторые определенные значения (т.е. отличные от NULL), можно использовать аналогичное выражение, но с логическим оператором NOT (не):

SELECT Имя, Адрес, Регион

FROM Клиенты

WHERE Адрес IS NOT NULL;

Не следует использовать предикаты сравнения с NULL, такие как "Адрес=NULL"

Сравнивает строковое выражение с шаблоном в выражении SQL.

Синтаксис

выражение Like "шаблон "

Синтаксис оператора Like включает в себя следующие компоненты:

Замечания

Оператор Like можно использовать для поиска значений полей, соответствующих указанному шаблону. В качестве шаблона можно задать полное значение (например, Like “Smith” ) или использовать подстановочные знаки, чтобы получить диапазон значений (например, Like “Sm*”) ).

В выражениях оператор Like может использоваться для сравнения значения поля со строковым выражением. Например, если в запросе SQL указать Like “C*” , запрос вернет все значения полей, начинающиеся на букву C. В запросе с параметрами можно предложить пользователю ввести шаблон для поиска.

Следующий пример возвращает данные, которые начинаются с буквы P, за которой идут любая буква от A до F и три цифры:

Like “P###”

В следующей таблице показано, как использовать оператор Like для проверки на соответствие различным шаблонам:

Тип совпадения	Шаблон	Соответствие	Нет соответствия (возвращает значение "Истина")
Несколько символов		аа, aБa, aБББa
	aбв, AAББ, Цaб
Особые символы
Несколько символов		aбвгдеё, aбв
Один символ		ааа, а3а, aБa
Одна цифра		а0а, а1а, а2а
Диапазон символов
Вне диапазона
Смешанный

LIKE оператор используется в предложении WHERE для поиска определенного шаблона в колонке.

LIKE Оператор SQL

LIKE оператор используется для поиска определенного шаблона в колонке.

SQL LIKE Синтаксис

Демо-версия базы данных

В этом уроке мы будем использовать хорошо известную базу данных Борей.

Ниже приводится подборка из "Customers" таблицы:

Пользовательский ИД	Имя Клиента	Контактное лицо	Адрес	город	Почтовый индекс	Страна
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitucion 2222	Mexico D.F.	05021	Mexico
3	Antonio Moreno Taqueria	Antonio Moreno	Mataderos 2312	Mexico D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbkop	Christina Berglund	Berguvsvagen 8	Lulea	S-958 22	Sweden

Примеры SQL LIKE оператора

Следующий SQL - оператор выбирает всех клиентов с городом, начиная с буквы "s" :

Совет: "%" знак используется для определения маски (недостающие буквы) до и после рисунка. Вы узнаете больше о знаках подстановки в следующей главе.

Следующий SQL - оператор выбирает всех клиентов с городом заканчивая буквой "s" :

Следующий SQL - оператор выбирает всех клиентов со страной, содержащей шаблон "land" :

Использование ключевого слова не позволяет выбрать записи, которые не соответствуют шаблону.

Следующий SQL - оператор выбирает всех клиентов с страна не содержащая шаблон "land" .