Как использовать like в sql

Оператор LIKE применяется для фильтрации строк по шаблону в запросах SELECT, UPDATE и DELETE. Он особенно полезен при работе с текстовыми данными, когда требуется найти значения, содержащие определённые фрагменты, начинающиеся или заканчивающиеся на заданные символы. В отличие от строгого сравнения через оператор =, LIKE позволяет использовать шаблоны с символами подстановки.

Основные подстановочные знаки – это % и _. Первый заменяет любое количество символов, включая ноль, а второй – только один символ. Например, выражение WHERE name LIKE ‘А%’ выберет все строки, где значение поля name начинается с буквы «А». Конструкция LIKE ‘_ена’ найдёт слова длиной в четыре символа, где последние три составляют «ена».

При использовании LIKE важно учитывать регистр символов. В большинстве реализаций SQL, таких как MySQL и SQLite, поиск нечувствителен к регистру, тогда как в PostgreSQL – чувствителен, если не указано иное. Для обхода этой особенности применяют функции LOWER() или UPPER(), приводя обе стороны сравнения к одному регистру.

LIKE часто используют вместе с оператором NOT, чтобы исключить значения, подходящие под определённый шаблон. Например, запрос WHERE email NOT LIKE ‘%@gmail.com’ позволит выбрать все записи, кроме тех, где адреса электронной почты принадлежат домену Gmail.

Синтаксис оператора LIKE и базовые принципы сравнения строк

Оператор LIKE используется в выражениях WHERE для поиска строк по шаблону. Он поддерживает два подстановочных символа: % и _. Символ % обозначает любое количество символов (включая ноль), а _ – ровно один символ.

Базовый синтаксис:

SELECT * FROM table_name WHERE column_name LIKE 'pattern';

Пример:

SELECT name FROM users WHERE name LIKE 'A%'; – выбирает строки, где значение поля name начинается с буквы «A».
SELECT name FROM users WHERE name LIKE '%son'; – возвращает записи, оканчивающиеся на «son».
SELECT name FROM users WHERE name LIKE '_at'; – находит трёхсимвольные значения, где вторая и третья буквы – «a» и «t».

Сравнение строк через LIKE чувствительно к регистру в зависимости от настроек СУБД. В MySQL чувствительность определяется колляцией столбца. Например, utf8_general_ci игнорирует регистр, а utf8_bin – учитывает его. В PostgreSQL можно использовать оператор ILIKE для нечувствительного к регистру поиска.

При использовании шаблонов внутри параметризованных запросов (например, в Python или PHP) следует добавлять символы % вручную, чтобы избежать потери части шаблона.
Пример для Python (через psycopg2):
cursor.execute("SELECT * FROM users WHERE name LIKE %s", ('%' + pattern + '%',)).

При необходимости экранировать символы % и _ применяется ключевое слово ESCAPE.

Пример:

SELECT * FROM files WHERE name LIKE '%\_%' ESCAPE '\'; – ищет строки, содержащие символ подчёркивания.

Использование символов подстановки % и _ при поиске шаблонов

Символ % заменяет любое количество символов, включая ноль. Он используется, когда нужно найти строки, содержащие часть заданного выражения. Например, запрос SELECT * FROM users WHERE name LIKE 'А%'; вернёт все записи, где имя начинается с буквы «А» – «Андрей», «Алина», «Артур» и т. д.

Символ _ заменяет ровно один символ. Это удобно при поиске строк фиксированной длины или с определённой структурой. Например, SELECT * FROM codes WHERE code LIKE 'A_1'; вернёт значения вроде «AB1», «AC1», но не «A1» или «A123».

Комбинация % и _ позволяет задавать точные шаблоны. Запрос SELECT * FROM files WHERE filename LIKE '_%.txt'; найдёт все текстовые файлы, где перед расширением есть как минимум один символ, например «a.txt» или «doc1.txt».

Чтобы искать символы % или _ как обычные знаки, используется экранирование. Например: SELECT * FROM comments WHERE text LIKE '%\_%' ESCAPE '\'; – вернёт строки, где действительно присутствует символ подчёркивания.

При работе с LIKE стоит учитывать регистрозависимость, зависящую от параметров сопоставления (collation). Для нечувствительного поиска применяют, например, COLLATE utf8_general_ci.

Поиск значений, начинающихся или заканчивающихся на заданную подстроку

Для поиска строк, начинающихся с определённой последовательности символов, используется шаблон с подстановочным знаком процента (%) в конце. Например, запрос:

SELECT * FROM users WHERE name LIKE 'Алекс%';

вернёт все записи, где поле name начинается с «Алекс»: «Алексей», «Александр», «Алексия». Символ % обозначает любую последовательность символов после указанной подстроки.

Если нужно найти строки, заканчивающиеся на определённую подстроку, знак % ставится в начале. Пример:

SELECT * FROM files WHERE filename LIKE '%.txt';

Такой запрос выберет все файлы с расширением .txt. Здесь % заменяет произвольный набор символов перед «.txt».

Для точной фильтрации рекомендуется учитывать регистр символов. В MySQL сравнение по LIKE нечувствительно к регистру для текстовых столбцов с кодировкой utf8_general_ci, а в PostgreSQL оператор LIKE чувствителен к регистру, поэтому для нечувствительного поиска используется ILIKE.

Если требуется исключить значения, начинающиеся или заканчивающиеся на подстроку, применяется оператор NOT LIKE. Пример:

SELECT * FROM products WHERE code NOT LIKE 'AB%';

Этот запрос отберёт все записи, у которых значение поля code не начинается с «AB».

При работе с символом подстановки _ можно находить строки по фиксированному количеству символов. Например, '_23%' найдёт значения, где вторым и третьим символами идут цифры «23».

Комбинирование условий LIKE с оператором AND и OR

Оператор LIKE часто применяют совместно с AND и OR для уточнения выборок. Это позволяет искать записи, удовлетворяющие нескольким шаблонам одновременно или хотя бы одному из них.

Пример с оператором AND:

SELECT *
FROM products
WHERE name LIKE '%кофе%'
AND description LIKE '%арабика%';

Такой запрос вернёт только те строки, где в названии встречается слово «кофе» и в описании – «арабика». Оператор AND объединяет оба условия, требуя совпадения обоих шаблонов.

Пример с оператором OR:

SELECT *
FROM clients
WHERE city LIKE 'Санкт%'
OR city LIKE 'Мос%';

Здесь результат включит клиентов из городов, начинающихся на «Санкт» или «Мос». OR расширяет диапазон поиска, возвращая строки, где выполняется хотя бы одно условие.

Рекомендации при комбинировании:

• Заключайте каждое выражение LIKE в скобки при сложных логических связках, чтобы избежать двусмысленности при приоритете операций.
• Используйте LOWER() или UPPER() при необходимости сделать поиск нечувствительным к регистру.
• Оптимизируйте шаблоны: начальные символы % затрудняют использование индексов и замедляют поиск.

Комбинированный пример:

SELECT *
FROM orders
WHERE (customer_name LIKE 'Иван%' OR customer_name LIKE 'Петр%')
AND status LIKE '%оплачен%';

Запрос отберёт оплаченные заказы, где имя клиента начинается с «Иван» или «Петр». Такое комбинирование часто используют при поиске по клиентским и транзакционным данным.

Применение LIKE в запросах с JOIN и подзапросами

Оператор LIKE часто используется не только в простых фильтрах, но и при объединении таблиц или работе с подзапросами. Это позволяет отбирать строки по частичному совпадению текста в более сложных структурах данных.

Пример использования LIKE с объединением таблиц:

SELECT c.customer_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_name LIKE 'Иван%';

В этом запросе объединяются таблицы customers и orders, после чего фильтрация выполняется по имени клиента. Такой подход удобен, когда поиск по шаблону должен применяться только после соединения.

При работе с подзапросами LIKE позволяет фильтровать результаты внешнего запроса на основе выборки из другой таблицы:

SELECT product_name
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name LIKE '%электро%'
);

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

Чтобы повысить производительность, фильтрацию с LIKE можно выполнять до объединения, если структура данных это позволяет. Например:

SELECT c.customer_name, o.order_id
FROM (
SELECT * FROM customers WHERE customer_name LIKE 'А%'
) AS c
JOIN orders o ON c.customer_id = o.customer_id;

Такой вариант уменьшает количество строк, участвующих в JOIN, и снижает нагрузку на сервер.

Регистрозависимость и использование COLLATE для управления сравнениями

В SQL оператор LIKE по умолчанию учитывает настройки сравнения (collation) столбца. В зависимости от используемой кодировки поиск может быть регистрозависимым или регистронезависимым.

Например, в SQL Server строки с collation Latin1_General_CS_AS будут чувствительны к регистру, а с Latin1_General_CI_AS – нет:

SELECT * FROM Users
WHERE Username LIKE 'Alex%';

Если Username имеет CS_AS, запрос вернёт только точное совпадение с «Alex», игнорируя «alex» или «ALEX».

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

SELECT * FROM Users
WHERE Username COLLATE Latin1_General_CI_AS LIKE 'alex%';

Этот запрос вернёт все варианты: «Alex», «alex», «ALEX».

Рекомендации по использованию:

• Для регистронезависимого поиска используйте CI (Case Insensitive) collation, особенно при фильтрации данных для пользовательского ввода.
• Для строго регистрозависимого поиска применяйте CS (Case Sensitive) collation, если важен точный формат символов.
• COLLATE можно применять к столбцам, константам и даже результатам функций, например: LOWER(Username) COLLATE Latin1_General_CI_AS.
• При массовой обработке данных регистронезависимое сравнение может снижать производительность, учитывайте это при построении индексов.
• Старайтесь использовать COLLATE только там, где это необходимо, чтобы избежать конфликта с настройками базы и индексами.

Пример использования COLLATE с подстановочными символами для гибкого поиска:

SELECT * FROM Products
WHERE ProductName COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%кабель%';

Такой запрос найдёт все строки, содержащие «Кабель», «кабель» или «КАБЕЛЬ», независимо от регистра.

COLLATE обеспечивает точное управление сравнением и совместимость с различными языковыми наборами, что особенно важно при работе с мультиязычными данными.

LIKE против ILIKE и REGEXP: различия и области применения

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

ILIKE – расширение LIKE в PostgreSQL, игнорирующее регистр символов. Используется, когда требуется сопоставление без учёта верхнего и нижнего регистра, например: поиск по именам пользователей вне зависимости от того, как они введены.

REGEXP или ~ в PostgreSQL поддерживает регулярные выражения, обеспечивая гибкий поиск сложных шаблонов, недоступных через LIKE или ILIKE. Позволяет проверять наличие последовательностей символов, диапазонов, повторов и опциональных элементов, например: проверка формата email или номера телефона.

Выбор между операторами определяется задачей: LIKE подходит для простых проверок с фиксированным шаблоном и чувствительностью к регистру, ILIKE – для нечувствительных к регистру поисков, REGEXP – для сложных шаблонов или проверки строгих форматов.

Использование REGEXP требует осторожности: сложные выражения могут значительно замедлить выполнение запросов на больших таблицах. LIKE с ведущим символом % также снижает производительность, так как индекс использовать невозможно.

Рекомендации: для простого поиска подстрок используйте LIKE, если регистр важен, или ILIKE, если нет. Для валидации форматов и сложных шаблонов – REGEXP, при этом по возможности оптимизируйте выражения и избегайте лишних повторов символов подстановки.

Оптимизация производительности запросов с LIKE при больших объёмах данных

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

1. Применять LIKE 'abc%' вместо LIKE '%abc%', когда известно начало строки. В этом случае индексы по столбцу будут использоваться, что снижает нагрузку на СУБД.

2. Создавать полнотекстовые индексы для колонок с большим текстовым содержимым. В PostgreSQL это GIN/GIN_trgm, в MySQL – FULLTEXT. Они позволяют выполнять поиск подстрок быстрее, чем обычный LIKE с подстановками.

3. Разделять столбцы на логические части, если известно, что поиск чаще выполняется по определённой секции текста. Это уменьшает объём данных для сканирования.

4. Использовать материализованные представления с предвычисленными колонками для часто ищущихся шаблонов. Это снижает количество динамических вычислений при больших таблицах.

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

Вопрос-ответ:

Для чего используется оператор LIKE в SQL?

Оператор LIKE применяется для поиска строк, которые соответствуют определённому шаблону. Он полезен, когда точное значение неизвестно или требуется выбрать данные с похожими признаками. Например, можно выбрать все имена пользователей, начинающиеся на «А», используя условие WHERE name LIKE ‘А%’. Символ % обозначает любое количество любых символов, а _ заменяет ровно один символ.

Какие символы подстановки поддерживает оператор LIKE и как они работают?

В SQL чаще всего используют два символа подстановки: % и _. Символ % соответствует любой последовательности символов, включая пустую строку. Символ _ соответствует ровно одному символу. Например, выражение LIKE ‘Sm_th%’ найдет строки, начинающиеся с «Sm», далее один любой символ, затем «th», и далее любое количество символов. Эти символы позволяют строить гибкие условия поиска.

Можно ли использовать LIKE для поиска без учёта регистра?

Поведение LIKE зависит от настроек базы данных. В некоторых системах, таких как MySQL с кодировкой utf8_general_ci, поиск не чувствителен к регистру. В других случаях, например в PostgreSQL, поиск с LIKE чувствителен к регистру, и для нечувствительного поиска используют ILIKE. Таким образом, для корректного поиска важно понимать, как именно база обрабатывает регистр символов.

Почему при использовании LIKE поиск может работать медленно на больших таблицах?

Если шаблон начинается с символа %, база данных не может использовать индекс для ускорения поиска, потому что неизвестно, с чего начнётся совпадение. Например, условие LIKE ‘%example’ заставляет проверять каждую строку таблицы. Чтобы ускорить запросы, лучше избегать ведущего % или использовать полнотекстовый поиск или специальные индексы, если требуется частый поиск по подстроке.