Теория: Фильтрация строк

Представим, что мы хотим собрать некоторую статистику по пользователям нашей системы - найти похожие фамилии, начинающиеся с "Sch" - Schuster, Schumm, Schmitt. Оператор сравнения = позволит найти только точное соответствие:

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name = 'Schumm';

View on DB Fiddle

Такой запрос выберет всех пользователей по фамилии Schumm. Но как быть с остальными фамилиями? Можно создать много подобных запросов на точное соответствие фамилии, а затем объединить результаты, но это неудобно и неэффективно. Родственных фамилий может быть много, а какие-то мы можем упустить.

Как фильтровать по подстроке

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

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name LIKE 'Sch%';

View on DB Fiddle

В приведенном шаблоне символ % заменяет любое количество любых символов. В том числе и ноль символов.

Это значит, что в нашем запросе будут выбраны строки, в которых поле last_name начинается на «Sch», а дальше идет любое количество любых символов. Например, этому условию будут удовлетворять фамилии Schuster, Schumm, Schmitt и другие.

Символ % можно поставить и в начало шаблона:

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name LIKE '%man';

View on DB Fiddle

В этом запросе будут выбраны все пользователи, чьи фамилии оканчиваются на «man»: Wyman, Herman и другие.

Символ % можно также использовать и в середине шаблона оператора LIKE:

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name LIKE 'Sch%t';

View on DB Fiddle

Этот запрос выберет пользователей с фамилиями, которые начинаются на «Sch» и заканчиваются на «t» — например Schmitt.

Попробуем повторить последний запрос, но в шаблоне укажем фамилию с маленькой буквы:

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name LIKE 'sch%t';

View on DB Fiddle

Такой запрос не выберет ничего, потому что шаблон подстроки регистрозависимый, а фамилии хранятся в базе с большой буквой в начале. Чтобы не задумываться о регистре букв в шаблоне можно воспользоваться оператором ILIKE, который ищет строки без учета регистра

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name ILIKE 'sch%t';

View on DB Fiddle

Такой запрос более универсален и выберет строки с фамилиями, записанными как строчными, так и заглавными буквами: Schmitt, schmitt, SCHMITT, SchMiTt.

Кроме поиска строк, подходящих под шаблон, можно сделать наоборот — вывести строки, на заданный шаблон совсем не похожие. Это делается с помощью оператора NOT LIKE. Например, мы хотим выбрать всех пользователей, фамилия которых не оканчивается на "man".

SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name NOT LIKE '%man';

View on DB Fiddle

Такой запрос вернет пользователей, чьи фамилии имеют любые окончания кроме "man".

Выводы

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