Предикат LIKE
Синтаксис:
LIKE::=
<Выражение для вычисления значения строки>
[NOT] LIKE <Выражение для вычисления значения строки>
[ESCAPE <символ>]
Предикат LIKE сравнивает строку, указанную в первом выражении, для вычисления значения строки, называемого проверяемым значением, с образцом, который определен во втором выражении для вычисления значения строки. В образце разрешается использовать два трафаретных символа:
- символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении;
- символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении.
Если проверяемое значение соответствует образцу с учетом трафаретных символов, то значение предиката равно TRUE. Ниже приводится несколько примеров написания образцов.
Образец | Описание |
---|---|
‘abc%’ | Любые строки, которые начинаются с букв «abc» |
‘abc_’ | Строки длиной строго 4 символа, причем первыми символами строки должны быть «abc» |
‘%z’ | Любая последовательность символов, которая обязательно заканчивается символом «z» |
‘%Rostov%’ | Любая последовательность символов, содержащая слово «Rostov» в любой позиции строки |
‘% % %’ | Текст, содержащий не менее 2-х пробелов, например, “World Wide Web” |
Пример 5.4.1
SELECT *
FROM Ships
WHERE class LIKE '%o' ;
[[ column ]] |
---|
[[ value ]] |
Результатом выполнения запроса будет следующая таблица:
name | class | launched |
---|---|---|
Haruna | Kongo | 1916 |
Hiei | Kongo | 1914 |
Kirishima | Kongo | 1915 |
Kongo | Kongo | 1913 |
Musashi | Yamato | 1942 |
Yamato | Yamato | 1941 |
Пример 5.4.2
SELECT *
FROM Ships
WHERE class NOT LIKE '%go'
AND class LIKE '%o' ;
[[ column ]] |
---|
[[ value ]] |
Name | Class | launched |
---|---|---|
Musashi | Yamato | 1942 |
Yamato | Yamato | 1941 |
Если искомая строка содержит трафаретный символ, то следует задать управляющий символ в предложении ESCAPE. Этот управляющий символ должен использоваться в образце перед трафаретным символом, сообщая о том, что последний следует трактовать как обычный символ. Например, если в некотором поле следует отыскать все значения, содержащие символ «_», то шаблон ‘%_%’ приведет к тому, что будут возвращены все записи из таблицы. В данном случае шаблон следует записать следующим образом:
'%#_%' ESCAPE '#'
Для проверки значения на соответствие строке «25%» можно воспользоваться таким предикатом:
LIKE '25|%' ESCAPE '|'
Следует заметить, что MySQL и PostgreSQL допускают помимо ESCAPE-символа экранирование трафаретных символов с помощью обратного слэша (\) в стиле некоторых языков программирования общего назначения. Для этих СУБД последние два шаблона можно записать так:
'%\_%'
'25\%'
Несмотря на краткость такой записи, она несет в собе потенциальную проблему при переходе на другую платформу.
Истинностное значение предиката LIKE присваивается в соответствии со следующими правилами:
- если либо проверяемое значение, либо образец, либо управляющий символ есть NULL, истинностное значение равно UNKNOWN;
- в противном случае, если проверяемое значение и образец имеют нулевую длину, истинностное значение равно TRUE;
- в противном случае, если проверяемое значение соответствует шаблону, то предикат LIKE равен TRUE;
- если не соблюдается ни одно из перечисленных выше условий, предикат LIKE равен FALSE.
Рекомендуемые упражнения: 44, 45, 74
Предикат LIKE и регулярные выражения
Предикат LIKE в его стандартной редакции не поддерживает регулярных выражений, хотя ряд реализаций (в частности, Oracle) допускает их использование, расширяя возможности стандарта.
В SQL Server, начиная с версии 2005, использование регулярных выражений возможно через CLR, т.е. посредством языков Visual Studio, которые могут использоваться для написания хранимых процедур и функций.
Однако в Transact-SQL, помимо стандартных символов-шаблонов ("%" и “_”), существует еще пара символов, которые делают этот предикат LIKE более гибким инструментом. Этими символами являются:
[ ] - одиночный символ из набора символов (например, [zxy]) или диапазона ([a-z]), указанных в квадратных скобках. При этом можно перечислить сразу несколько диапазонов (например, [0-9a-z]);
^ - который в сочетании с квадратными скобками исключает из поискового образца символы из набора или диапазона.
Поясним использование этих символов на примерах.
SELECT * FROM
(
SELECT '5%' name UNION ALL
SELECT '55' UNION ALL
SELECT '5%%' UNION ALL
SELECT '3%%' UNION ALL
SELECT 'a5%%' UNION ALL
SELECT 'abc' UNION ALL
SELECT 'abc 5% cde' UNION ALL
SELECT '5c2e' UNION ALL
SELECT 'C2H5OH' UNION ALL
SELECT 'C25OH' UNION ALL
SELECT 'C54OH'
) x
/* 1 */
--WHERE name LIKE'5%' -- начинается с 5
/* 2 */
--WHERE name LIKE '5[%]' -- 5%
/* 3 */
--WHERE name LIKE '5|%' ESCAPE '|'-- 5%
/* 4 */
--WHERE name LIKE '%5|%%' ESCAPE '|' -- 5% в любом месте строки
/* 5 */
--WHERE name LIKE '[0-9][a-zA-Z]%' -- первая цифра, вторая буква
/* 6 */
--WHERE name LIKE '[a-z][0-9]%' -- первая буква, вторая цифра
/* 7 */
--WHERE name LIKE '[^0-9]%' -- начинается не на цифру.
/* 8 */
--WHERE name LIKE '%[02468]%' -- содержит четную цифру.
/* 9 */
--WHERE name LIKE '%[02468][13579]%' -- комбинация четная-нечетная.
[[ column ]] |
---|
[[ value ]] |
В данном запросе генерируются некоторые данные, для поиска в которых используется предикат LIKE. Девять примеров - это соответственно 9 закомментированных предложений WHERE. Для проверки результатов выполнения запроса на сайте предварительно снимите комментарий с одной из строк, начинающихся с WHERE. Тем, кто не может использовать сайт, приведу здесь результаты выполнения этих примеров.
1. Все строки, которые начинаются с 5:
name |
---|
5% |
55 |
5%% |
5c2e |
2. Поиск строки ‘5%’. Символ в скобках воспринимается как обычный единичный символ:
name |
---|
5% |
3. Другое решение, аналогичное второму, но использующее ESCAPE-символ, указывающий, что знак % следует воспринимать как обычный символ.
4. Поиск подстроки ‘5%’, находящейся в любом месте строки:
name |
---|
5% |
5%% |
a5%% |
abc 5% cde |
5. Поиск строки, у которой первый символ является цифрой, а второй - буквой:
name |
---|
5c2e |
6. Поиск строки, у которой первый символ является буквой, а второй - цифрой. Вариант для регистронезависимого сравнения:
name |
---|
a5%% |
C2H5OH |
C25OH |
C54OH |
7. Поиск строки, которая начинается не с цифры:
name |
---|
a5%% |
abc |
abc 5% cde |
C2H5OH |
C25OH |
C54OH |
8. Поиск строки, которая содержит четную цифру:
name |
---|
5c2e |
C2H5OH |
C25OH |
C54OH |
9. Поиск строки, которая содержит подряд идущие четную и нечетную цифры:
name |
---|
C25OH |
Рекомендуемые упражнения: 35