Подстановочные знаки в SQL: оператор LIKE и шаблоны %, _, [ ]

Подстановочные знаки (wildcards) — это специальные символы в шаблоне оператора LIKE, которые заменяют собой один или несколько произвольных символов при поиске по строке. Их используют вместе с WHERE, чтобы искать значения не по точному совпадению, а по образцу: строки, которые начинаются, заканчиваются или содержат заданный фрагмент.

Ниже разберу два стандартных знака % и _, экранирование через ESCAPE, диалект-специфичные диапазоны [ ] в SQL Server и сведу в таблицу, что из этого работает в MySQL, PostgreSQL, SQLite и Access. Все запросы на % и _ прогнаны на SQLite, диапазоны [ ] помечены как специфичные для SQL Server.

Оператор LIKE и знаки % и _

Два подстановочных знака входят в стандарт SQL и работают во всех распространенных СУБД:

• % — заменяет любую последовательность символов, в том числе пустую (ноль и больше символов);
• _ — заменяет ровно один любой символ (не ноль и не два).

Возьмем таблицу клиентов и разберем шаблоны на ней. Пример полный: его можно скопировать и запустить целиком.

WITH customer(first_name, phone, country) AS (VALUES
  ('Alfred',   '030-0074321',    'Germany'),
  ('Ana',      '(5) 555-3932',   'Mexico'),
  ('Antonio',  '(5) 555-3392',   'Mexico'),
  ('Thomas',   '(171) 555-7788', 'UK'),
  ('Christina','0921-12 34 65',  'Sweden'))
SELECT first_name FROM customer WHERE first_name LIKE 'a%';
-- вернет: Alfred, Ana, Antonio

Шаблон 'a%' означает «начинается с буквы a, дальше что угодно». Обратите внимание: в примере совпали и имена с заглавной A. Это зависит от диалекта и настроек сравнения, к регистру вернусь ниже.

Знак % в середине шаблона ловит фрагмент в любом месте строки. Найдем телефоны, где встречается цифра 2:

WITH customer(first_name, phone) AS (VALUES
  ('Alfred',   '030-0074321'),
  ('Ana',      '(5) 555-3932'),
  ('Antonio',  '(5) 555-3392'),
  ('Thomas',   '(171) 555-7788'),
  ('Christina','0921-12 34 65'))
SELECT first_name, phone FROM customer WHERE phone LIKE '%2%';
-- вернет 4 строки: Alfred, Ana, Antonio, Christina (у Thomas двойки нет)

Знак _ фиксирует длину: он заменяет строго один символ. Найдем страну, у которой перед фрагментом exico стоит ровно одна любая буква:

WITH customer(country) AS (VALUES
  ('Germany'),('Mexico'),('Mexico'),('UK'),('Sweden'))
SELECT DISTINCT country FROM customer WHERE country LIKE '_exico';
-- вернет: Mexico

Разница между знаками важна на практике: '_exico' совпадет только со строкой из шести символов, а '%exico' — с любой строкой, которая заканчивается на exico, независимо от длины.

Экранирование: как искать сами символы % и _

Отдельная частая задача — найти строку, в которой физически стоит знак процента или подчеркивания. Если написать их прямо в шаблоне, СУБД примет их за подстановочные и вернет не то. Спасает ключевое слово ESCAPE: оно назначает символ-экран, после которого % и _ считаются обычными буквами.

WITH promo(code) AS (VALUES ('SALE50%'), ('SALE100'), ('BLACK_FRIDAY'))
SELECT code FROM promo WHERE code LIKE '%50\%%' ESCAPE '\';
-- вернет: SALE50%  (искали строку, где реально есть "50%")

Здесь \% — это буквальный процент, а обрамляющие % остаются подстановочными. Тем же приемом ищут строки с настоящим подчеркиванием:

WITH promo(code) AS (VALUES ('SALE50%'), ('SALE100'), ('BLACK_FRIDAY'))
SELECT code FROM promo WHERE code LIKE '%\_%' ESCAPE '\';
-- вернет: BLACK_FRIDAY  (единственная строка с символом "_")

Символ-экран можно выбрать любой, лишь бы он не встречался в самих данных как часть шаблона. В стандарте SQL символ-экран без ESCAPE не задан, поэтому клаузу лучше указывать явно. В MySQL обратный слэш \ работает экраном и по умолчанию, но полагаться на это между СУБД не стоит.

Диапазоны и наборы [ ] — только в SQL Server и Sybase

Квадратные скобки задают набор допустимых символов на одной позиции, как класс символов в регулярных выражениях. Это расширение конкретных СУБД, а не часть стандартного LIKE:

• [abc] — один символ из перечисленных (a, b или c);
• [a-c] — один символ из диапазона (a, b или c);
• [^a-c] — один символ, которого нет в диапазоне (отрицание).

Важно: диапазоны [ ] и [^ ] в операторе LIKE понимают только Microsoft SQL Server (T-SQL) и Sybase. В стандартном LIKE MySQL, PostgreSQL и SQLite их нет — там скобки трактуются буквально, как обычные символы, и запрос молча вернет не то, а не выдаст ошибку. Это опаснее явной ошибки, поэтому диалект надо держать в голове.

Синтаксис для SQL Server — выбрать страны, которые начинаются на S или G:

-- ТОЛЬКО SQL Server / Sybase. На SQLite/MySQL/PostgreSQL вернет пустой
-- результат: там [SG] - это буквальные символы "[", "S", "G", "]".
WITH customer(country) AS (VALUES ('Germany'),('Mexico'),('UK'),('Sweden'))
SELECT country FROM customer WHERE country LIKE '[SG]%';
-- в SQL Server вернет: Germany, Sweden. На SQLite - пусто (проверено).

Отрицание в SQL Server пишут через ^ внутри скобок — выберем города, которые НЕ начинаются на буквы от A до C:

-- ТОЛЬКО SQL Server / Sybase.
WITH addresses(city) AS (VALUES ('Berlin'),('Madrid'),('London'),('Boston'))
SELECT city FROM addresses WHERE city LIKE '[^A-C]%';
-- в SQL Server вернет города НЕ на A, B, C: London, Madrid.
-- На SQLite - пусто (скобки трактуются буквально).

Здесь легко перепутать диалекты. В SQL Server отрицание — это [^...], а в Microsoft Access для того же используется восклицательный знак: [!...]. Access вообще стоит особняком: в режиме по умолчанию его подстановочные знаки — это * и ? вместо % и _. Поэтому один и тот же шаблон нельзя переносить между Access и остальными СУБД не глядя.

Различия СУБД: что где работает

Сводка по подстановочным знакам в операторе LIKE. Она отвечает на главный вопрос — можно ли перенести шаблон в вашу СУБД.

Ключевые выводы из таблицы: знаки % и _ переносимы всюду, а диапазоны [ ] в LIKE — нет. Если нужны классы символов в MySQL, берут REGEXP; в PostgreSQL — SIMILAR TO или оператор ~; в SQLite — GLOB. Учет регистра тоже различается: PostgreSQL по умолчанию сравнивает LIKE с учетом регистра и предлагает ILIKE, а SQL Server и MySQL зависят от collation столбца.

FAQ

Чем LIKE отличается от полнотекстового поиска? LIKE сравнивает строку по шаблону посимвольно и хорошо работает на префиксах ('abc%'). Поиск по подстроке ('%abc%') обычно не использует индекс и на больших таблицах медленный — для этого берут полнотекстовый поиск (FULLTEXT, tsvector, отдельные движки).

Ускоряет ли индекс запрос с LIKE? Обычный B-tree индекс помогает шаблону с якорем в начале ('abc%'), но не помогает шаблонам вида '%abc' и '%abc%', потому что неизвестно начало строки. В PostgreSQL для таких случаев есть триграммные индексы (pg_trgm).

Что вернет шаблон без подстановочных знаков? LIKE 'abc' без % и _ работает как проверка на равенство и вернет только строки, точно равные abc (с поправкой на правила сравнения и регистр диалекта).

Выводы

Подстановочные знаки в SQL — это инструмент поиска по образцу внутри оператора LIKE. Универсальный минимум — % (любая последовательность) и _ (ровно один символ); они работают во всех распространенных СУБД. Для поиска самих символов % и _ используют ESCAPE. Диапазоны [ ] и [^ ] — это расширение SQL Server и Sybase, в MySQL, PostgreSQL и SQLite их в LIKE нет, и вместо них применяют REGEXP, SIMILAR TO или GLOB. Перед переносом шаблона в другую СУБД сверяйтесь с таблицей выше, иначе запрос вернет неверный результат тихо, без ошибки.

Смежные темы: Группировка в SQL, Ключи в SQL-таблицах, Знакомство с SQLite.