Оператор LIKE в SQLite: поиск по шаблону и примеры запросов

Значение, которое нужно найти, может быть неизвестно заранее. Например, вы помните, что в названии трека присутствует слово elevator, но не можете вспомнить его полностью. Для таких случаев в SQLite предусмотрен оператор LIKE, который позволяет выполнять запросы на основе частичного совпадения строк

Синтаксис оператора LIKE

Чтобы выполнить запрос к данным на основе частичной информации, используйте оператор LIKE в предложении WHERE оператора SELECT следующим образом:

SELECT column_list
FROM table_name
WHERE column_1 LIKE pattern;

Оператор LIKE также можно использовать в предложении WHERE других операторов — DELETE и UPDATE

SQLite предоставляет два символа подстановки (wildcard) для построения шаблонов:

• Знак процента % — соответствует любой последовательности из нуля или более символов
• Подчёркивание _ — соответствует любому одиночному символу

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Шаблоны с символом процента %

Символ % — самый часто используемый подстановочный знак. Вот несколько примеров шаблонов, с которыми я регулярно сталкиваюсь на практике:

• s% — соответствует любой строке, начинающейся с s, например son и so
• %er — соответствует любой строке, заканчивающейся на er, например peter, clever
• %per% — соответствует любой строке, содержащей per, например percent и peeper

Шаблоны с символом подчёркивания _

Символ _ соответствует ровно одному символу на своей позиции:

• h_nt — соответствует hunt, hint и т. д.
• __pple — соответствует topple, supple, tipple и т. д.

Обратите внимание: оператор SQLite LIKE нечувствителен к регистру. Это означает, что "A" LIKE "a" возвращает true

Оператор LIKE чувствителен к регистру для символов Unicode вне диапазона ASCII

Примеры запросов с оператором LIKE в SQLite

Для демонстрации используется таблица tracks из примера базы данных

Чтобы найти треки, названия которых начинаются с литеральной строки Wild, используйте символ % в конце шаблона:

SELECT trackid, name
FROM tracks
WHERE name LIKE 'Wild%';

Чтобы найти треки, названия которых заканчиваются словом Wild, используйте % в начале шаблона:

Следующий оператор находит треки, названия которых содержат: ноль или более символов (%), затем Br, затем один символ (_), затем wn, и затем ноль или более символов (%):

SELECT trackid, name
FROM tracks
WHERE name LIKE '%Br_wn%';

SQLite LIKE с предложением ESCAPE

Если шаблон, с которым нужно выполнить сопоставление, содержит % или _, необходимо использовать символ экранирования (escape character) в предложении ESCAPE

column_1 LIKE pattern ESCAPE expression;

Когда вы указываете предложение ESCAPE, оператор LIKE вычисляет выражение, следующее за ключевым словом ESCAPE, как строку, состоящую из одного символа — символа экранирования

Этот символ экранирования можно использовать в шаблоне для включения литерального знака процента (%) или подчёркивания (_). Оператор LIKE интерпретирует % или _, следующие за символом экранирования, как литеральную строку, а не как символ подстановки

Предположим, нужно найти строку 10% в столбце таблицы. Однако SQLite интерпретирует символ % как символ подстановки. Поэтому необходимо экранировать его с помощью символа экранирования:

column_1 LIKE '%10\%%' ESCAPE '\';

В этом выражении оператор LIKE интерпретирует первый % и последний % как символы подстановки, а второй знак процента — как литеральный символ процента

В качестве символа экранирования можно использовать и другие символы, например /, @, $

Пошаговый пример с предложением ESCAPE

Рассмотрим полный пример, чтобы убедиться в разнице между запросом без ESCAPE и с ним. На мой взгляд, именно такой разбор «до и после» лучше всего закрепляет понимание механизма экранирования

Сначала создайте таблицу t с одним столбцом:

CREATE TABLE t( c TEXT
);

Затем вставьте несколько строк в таблицу t:

INSERT INTO t(c)
VALUES ('10% increase'), ('10 times decrease'), ('100% vs. last year'), ('20% increase next year');

После этого выполните запрос к данным из таблицы t:

SELECT * FROM t;

Результат:

c
----------------------
10% increase
10 times decrease
100% vs. last year
20% increase next year

Теперь попробуйте найти строку, значение в столбце c которой содержит литеральную строку 10%:

SELECT c
FROM t
WHERE c LIKE '%10%%';

Однако этот запрос возвращает строки, значения в столбце c которых содержат просто 10 — без учёта знака процента:

c
------------------
10% increase
10 times decrease
100% vs. last year

Чтобы получить правильный результат, используйте предложение ESCAPE, как показано в следующем запросе:

SELECT c
FROM t
WHERE c LIKE '%10\%%' ESCAPE '\';

Результирующий набор:

c
------------
10% increase

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

Частые ошибки при использовании LIKE в SQLite

При использовании оператора LIKE легко допустить несколько распространённых ошибок. Разберу каждую из них отдельно — в нашей практике все они встречались хотя бы раз

Забыть экранировать % и _ в данных. Если искомая строка содержит эти символы, запрос без ESCAPE вернёт неожиданно широкий набор строк — как в примере выше с 10%

Рассчитывать на чувствительность к регистру для ASCII-символов. Оператор LIKE в SQLite нечувствителен к регистру для символов ASCII, поэтому LIKE 'wild%' и LIKE 'Wild%' дадут одинаковый результат. Для Unicode-символов вне диапазона ASCII поведение противоположное

Использовать LIKE там, где нужен точный поиск. Если значение известно точно, лучше использовать оператор = — он работает быстрее, поскольку может задействовать индекс напрямую

Ставить % в начало шаблона без необходимости. Шаблон вида '%Wild' не позволяет SQLite использовать индекс по столбцу, что может существенно замедлить запрос на больших таблицах

Ответы на эти вопросы могут быть для вас полезными

Можно ли использовать оператор LIKE в предложениях DELETE и UPDATE? Да. Оператор LIKE работает в предложении WHERE любого оператора, который его поддерживает, включая DELETE и UPDATE

Чем отличается % от _ в шаблоне LIKE? Символ % соответствует любой последовательности из нуля или более символов, а _ — ровно одному символу на конкретной позиции

Как искать строку, которая буквально содержит символ %? Нужно использовать предложение ESCAPE и указать символ экранирования перед % в шаблоне. Например: LIKE '%10\%%' ESCAPE '\' найдёт строки, содержащие 10%

Учитывает ли SQLite LIKE регистр символов? Для символов ASCII — нет, LIKE нечувствителен к регистру. Для символов Unicode вне диапазона ASCII — да, LIKE чувствителен к регистру

Влияет ли позиция % в шаблоне на производительность запроса? Да. Если % стоит в начале шаблона, SQLite не может использовать индекс по столбцу и выполняет полное сканирование таблицы. Шаблоны вида 'Wild%' работают эффективнее, так как позволяют задействовать индекс