LIKE – проверка строки по шаблону

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

Этот оператор имеет следующий вид:

проверяемая_строка [NOT] LIKE строка_шаблон [ESCAPE отменяющий_символ]

 

В «строке_шаблон» могут применятся следующие специальные символы:

1.Знак подчеркивания «_» — говорит, что на его месте может стоять любой единичный символ

2.Знак процента «%» — говорит, что на его месте может стоять сколько угодно символов, в том числе и ни одного

Рассмотрим примеры с символом «%» (на практике, кстати он чаще применяется):

SELECT ID,Name

FROM Employees

WHERE Name LIKE 'Пет%' -- у кого имя начинается с букв "Пет"

 

SELECT ID,LastName

FROM Employees

WHERE LastName LIKE '%ов' -- у кого фамилия оканчивается на "ов"

 

SELECT ID,LastName

FROM Employees

WHERE LastName LIKE '%ре%' -- у кого фамилия содержит сочетание "ре"

 

Рассмотрим примеры с символом «_»:

SELECT ID,LastName

FROM Employees

WHERE LastName LIKE '_етров' -- у кого фамилия состоит из любого первого символа и последующих букв "етров"

 

SELECT ID,LastName

FROM Employees

WHERE LastName LIKE '____ов' -- у кого фамилия состоит из четырех любых символов и последующих букв "ов"

 

При помощи ESCAPE можно задать отменяющий символ, который отменяет проверяющее действие специальных символов «_» и «%». Данное предложение используется, когда в строке нужно непосредственно проверить наличие знака процента или знака подчеркивания.

Для демонстрации ESCAPE давайте занесем в одну запись мусор:

UPDATE Employees

SET

FirstName='Это_мусор, содержащий %'

WHERE ID=1005

 

И посмотрим, что вернут следующие запросы:

SELECT *

FROM Employees

WHERE FirstName LIKE '%!%%' ESCAPE '!' -- строка содержит знак "%"

 

SELECT *

FROM Employees

WHERE FirstName LIKE '%!_%' ESCAPE '!' -- строка содержит знак "_"

 

В случае, если требуется проверить строку на полное совпадение, то вместо LIKE лучше использовать просто знак «=»:

SELECT *

FROM Employees

WHERE FirstName='Петр'

 

На заметку.
В MS SQL в шаблоне оператора LIKE так же можно задать поиск по регулярным выражениям, почитайте о нем в интернете, в том случае, если вам станет недостаточно стандартных возможностей данного оператора.

В ORACLE для поиска по регулярным выражениям применяется функция REGEXP_LIKE.

 

Немного о строках

В случае проверки строки на наличие Unicode символов, нужно будет ставить перед кавычками символ N, т.е. N'…'. Но так как у нас в таблице все символьные поля в формате Unicode (тип nvarchar), то для этих полей можно всегда использовать такой формат. Пример:

SELECT ID,Name

FROM Employees

WHERE Name LIKE N'Пет%'

 

SELECT ID,LastName

FROM Employees

WHERE LastName=N'Петров'

 

Если делать правильно, при сравнении с полем типа varchar (ASCII) нужно стараться использовать проверки с использованием '…', а при сравнении поля с типом nvarchar (Unicode) нужно стараться использовать проверки с использованием N'…'. Это делается для того, чтобы избежать в процессе выполнения запроса неявных преобразований типов. То же самое правило используем при вставке (INSERT) значений в поле или их обновлении (UPDATE).

При сравнении строк стоит учесть момент, что в зависимости от настройки БД (collation), сравнение строк может быть, как регистро-независимым (когда 'Петров'='ПЕТРОВ'), так и регистро-зависимым (когда 'Петров'<>'ПЕТРОВ').
В случае регистро-зависимой настройки, если требуется сделать поиск без учета регистра, то можно, например, сделать предварительное преобразование правого и левого выражения в один регистр – верхний или нижний:

SELECT ID,Name

FROM Employees

WHERE UPPER(Name) LIKE UPPER(N'Пет%') -- или LOWER(Name) LIKE LOWER(N'Пет%')

 

SELECT ID,LastName

FROM Employees

WHERE UPPER(LastName)=UPPER(N'Петров') -- или LOWER(LastName)=LOWER(N'Петров')

 

Немного о датах

При проверке на дату, вы можете использовать, как и со строками одинарные кавычки '…'.

Вне зависимости от региональных настроек в MS SQL можно использовать следующий синтаксис дат 'YYYYMMDD' (год, месяц, день слитно без пробелов). Такой формат даты MS SQL поймет всегда:

SELECT ID,Name,Birthday

FROM Employees

WHERE Birthday BETWEEN '19800101' AND '19891231' -- сотрудники 80-х годов

ORDER BY Birthday

 

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

SELECT ID,Name,Birthday

FROM Employees

WHERE Birthday BETWEEN DATEFROMPARTS(1980,1,1) AND DATEFROMPARTS(1989,12,31)

ORDER BY Birthday

 

Так же есть аналогичная функция DATETIMEFROMPARTS, которая служит для задания Даты и Времени (для типа datetime).

Еще вы можете использовать функцию CONVERT, если требуется преобразовать строку в значение типа date или datetime:

SELECT

CONVERT(date,'12.03.2015',104),

CONVERT(datetime,'2014-11-30 17:20:15',120)

 

Значения 104 и 120, указывают какой формат даты используется в строке. Описание всех допустимых форматов вы можете найти в библиотеке MSDN задав в поиске «MS SQL CONVERT».

Функций для работы с датами в MS SQL очень много, ищите «ms sql функции для работы с датами».

Примечание. Во всех диалектах языка SQL свой набор функций по работе с датами и применяется свой подход по работе с ними.

 

Немного о числах и их преобразованиях

Информация этого раздела наверно больше будет полезна ИТ-специалистам. Если вы таковым не являетесь, а ваша цель просто научится писать запросы для получения из БД необходимой вам информации, то такие тонкости вам возможно и не понадобятся, но в любом случае можете бегло пройтись по тексту и взять что-то на заметку, т.к. если вы взялись за изучение SQL, то вы уже приобщаетесь к ИТ.

В отличие от функции преобразования CAST, в функции CONVERT можно задать третий параметр, который отвечает за стиль преобразования (формат). Для разных типов данных может использоваться свой набор стилей, которые могут повлиять на возвращаемый результат. Использование стилей мы уже затрагивали при рассмотрении преобразования строки функцией CONVERT в типы date и datetime.

Подробней про функции CAST, CONVERT и стили можно почитать в MSDN – «Функции CAST и CONVERT (Transact-SQL)»: msdn.microsoft.com/ru-ru/library/ms187928.aspx

Для упрощения примеров здесь будут использованы инструкции языка Transact-SQL – DECLARE и SET.

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

DECLARE @min_int int SET @min_int=-2147483648

DECLARE @max_int int SET @max_int=2147483647

 

SELECT

-- (-2147483648)

@min_int,CAST(@min_int AS float),CONVERT(float,@min_int),

 

-- 2147483647

@max_int,CAST(@max_int AS float),CONVERT(float,@max_int),

 

-- numeric(16,6)

@min_int/1., -- (-2147483648.000000)

@max_int/1. -- 2147483647.000000

 

Возможно не стоило указывать способ неявного преобразования, получаемого делением на (1.), т.к. желательно стараться делать явные преобразования, для большего контроля типа получаемого результата. Хотя, в случае, если мы хотим получить результат типа numeric, с указанным количеством цифр после запятой, то мы можем в MS SQL применить трюк с умножением целого значения на (1., 1.0, 1.00 и т.д):

DECLARE @int int SET @int=123

 

SELECT

@int*1., -- numeric(12, 0) - 0 знаков после запятой

@int*1.0, -- numeric(13, 1) - 1 знак

@int*1.00, -- numeric(14, 2) - 2 знака

 

-- хотя порой лучше сделать явное преобразование

CAST(@int AS numeric(20, 0)), -- 123

CAST(@int AS numeric(20, 1)), -- 123.0

CAST(@int AS numeric(20, 2)) -- 123.00

 

В некоторых случаях детали преобразования могут быть действительно важны, т.к. они влияют на правильность полученного результата, например, в случае, когда делается преобразование числового значения в строку (varchar). Рассмотрим примеры по преобразованию значений типа money и float в varchar:

-- поведение при преобразовании money в varchar

DECLARE @money money

SET @money = 1025.123456789 -- произойдет неявное преобразование в 1025.1235, т.к. тип money хранит только 4 цифры после запятой

 

SELECT

@money, -- 1025.1235

-- по умолчанию CAST и CONVERT ведут себя одинаково (т.е. грубо говоря применяется стиль 0)

CAST(@money as varchar(20)), -- 1025.12

CONVERT(varchar(20), @money), -- 1025.12

CONVERT(varchar(20), @money, 0), -- 1025.12 (стиль 0 - без разделителя тысячных и 2 цифры после запятой (формат по умолчанию))

 

CONVERT(varchar(20), @money, 1), -- 1,025.12 (стиль 1 - используется разделитель тысячных и 2 цифры после запятой)

CONVERT(varchar(20), @money, 2) -- 1025.1235 (стиль 2 - без разделителя и 4 цифры после запятой)

 

-- поведение при преобразовании float в varchar

DECLARE @float1 float SET @float1 = 1025.123456789

DECLARE @float2 float SET @float2 = 1231025.123456789

 

SELECT

@float1, -- 1025.123456789

@float2, -- 1231025.12345679

-- по умолчанию CAST и CONVERT ведут себя одинаково (т.е. грубо говоря применяется стиль 0)

-- стиль 0 - Не более 6 разрядов. По необходимости используется экспоненциальное представление чисел

-- при преобразовании в varchar здесь творятся действительно страшные вещи

CAST(@float1 as varchar(20)), -- 1025.12

CONVERT(varchar(20), @float1), -- 1025.12

CONVERT(varchar(20), @float1, 0), -- 1025.12

 

CAST(@float2 as varchar(20)), -- 1.23103e+006

CONVERT(varchar(20), @float2), -- 1.23103e+006

CONVERT(varchar(20), @float2, 0), -- 1.23103e+006

 

-- стиль 1 - Всегда 8 разрядов. Всегда используется экспоненциальное представление чисел.

-- этот стиль для float тоже не очень точен

CONVERT(varchar(20), @float1, 1), -- 1.0251235e+003

CONVERT(varchar(20), @float2, 1), -- 1.2310251e+006

 

-- стиль 2 - Всегда 16 разрядов. Всегда используется экспоненциальное представление чисел.

-- здесь с точностью уже получше

CONVERT(varchar(30), @float1, 2), -- 1.025123456789000e+003 - OK

CONVERT(varchar(30), @float2, 2) -- 1.231025123456789e+006 - OK

 

Как видно из примера, плавающие типы float, real в некоторых случаях действительно могут создать большую погрешность, особенно при перегонке в строку и обратно (такое может быть при разного рода интеграциях, когда данные, например, передаются в текстовых файлах из одной системы в другую).

Если нужно явно контролировать точность до определенного знака, более 4-х, то для хранения данных, порой лучше использовать тип decimal/numeric. Если хватает 4-х знаков, то можно использовать и тип money – он примерно соотвествует numeric(20,4).

-- decimal и numeric

DECLARE @money money SET @money = 1025.123456789 -- 1025.1235

DECLARE @float1 float SET @float1 = 1025.123456789

DECLARE @float2 float SET @float2 = 1231025.123456789

 

DECLARE @numeric numeric(28,9) SET @numeric = 1025.123456789

 

SELECT

CAST(@numeric as varchar(20)), -- 1025.12345679

CONVERT(varchar(20), @numeric), -- 1025.12345679

 

CAST(@money as numeric(28,9)), -- 1025.123500000

CAST(@float1 as numeric(28,9)), -- 1025.123456789

CAST(@float2 as numeric(28,9)) -- 1231025.123456789

 

Примечание.
С версии MS SQL 2008, можно использовать вместо конструкции:

DECLARE @money money

SET @money = 1025.123456789

 

Более короткий синтаксис инициализации переменных:

DECLARE @money money = 1025.123456789

 

 

Заключение второй части

В этой части, я постарался вспомнить и отразить наиболее важные моменты, касающиеся базового синтаксиса. Базовая конструкция – это костяк, без которого нельзя приступать к изучению более сложных конструкций языка SQL.