Представим, что мы хотим собрать некоторую статистику по пользователям нашей системы - найти похожие фамилии, начинающиеся с "Sch" - Schuster, Schumm, Schmitt. Оператор сравнения = позволит найти только точное соответствие:
SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name = 'Schumm';id |
first_name |
last_name |
birthday |
|
27 |
Jarrod |
Schumm |
Jasen_DAmore94@gmail.com |
2021-10-09T00:00:00.000Z |
View on DB Fiddle
Такой запрос выберет всех пользователей по фамилии Schumm. Но как быть с остальными фамилиями? Можно создать много подобных запросов на точное соответствие фамилии, а затем объединить результаты, но это неудобно и неэффективно. Родственных фамилий может быть много, а какие-то мы можем упустить.
Как фильтровать по подстроке
В языке SQL существует возможность фильтрации строки по шаблону - подстроке, которую мы хотим найти.
Для фильтрации текста по шаблону используется оператор LIKE, после которого мы указываем шаблон. В нашей задаче мы хотим найти людей, чьи фамилии начинаются на "Sch", поэтому шаблон будет выглядеть так:
SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name LIKE 'Sch%';id |
first_name |
last_name |
birthday |
|
22 |
Princess |
Schmitt |
Daniella.Pfeffer46@yahoo.com |
2022-01-10T00:00:00.000Z |
27 |
Jarrod |
Schumm |
Jasen_DAmore94@gmail.com |
2021-10-09T00:00:00.000Z |
35 |
Hershel |
Schuster |
Jaleel.Littel@gmail.com |
2022-02-26T00:00:00.000Z |
64 |
Laron |
Schoen |
Lea2@hotmail.com |
2021-11-13T00:00:00.000Z |
87 |
Jannie |
Schuster |
Tommie20@gmail.com |
2021-08-07T00:00:00.000Z |
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';id |
first_name |
last_name |
birthday |
|
67 |
Ofelia |
Stroman |
Cecile.Skiles@gmail.com |
2021-12-21T00:00:00.000Z |
91 |
Cassidy |
Wyman |
Rupert33@hotmail.com |
2022-03-25T00:00:00.000Z |
97 |
Bernhard |
Herman |
Vesta.Flatley16@hotmail.com |
2021-12-20T00:00:00.000Z |
View on DB Fiddle
В этом запросе будут выбраны все пользователи, чьи фамилии оканчиваются на «man»: Wyman, Herman и другие.
Символ % можно также использовать и в середине шаблона оператора LIKE:
SELECT id, first_name, last_name, email, birthday FROM users WHERE last_name LIKE 'Sch%t';id |
first_name |
last_name |
birthday |
|
22 |
Princess |
Schmitt |
Daniella.Pfeffer46@yahoo.com |
2022-01-10T00:00:00.000Z |
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';id |
first_name |
last_name |
birthday |
|
22 |
Princess |
Schmitt |
Daniella.Pfeffer46@yahoo.com |
2022-01-10T00:00:00.000Z |
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';id |
first_name |
last_name |
birthday |
|
1 |
Lucienne |
Feil |
Trevion53@yahoo.com |
2022-05-25T00:00:00.000Z |
2 |
Ramiro |
Wolf |
Baylee52@yahoo.com |
2022-01-13T00:00:00.000Z |
3 |
Maureen |
Romaguera |
Casimer_Cronin@yahoo.com |
2022-04-21T00:00:00.000Z |
4 |
Jennyfer |
Flatley |
Angelita.Altenwerth96@hotmail.com |
2021-10-31T00:00:00.000Z |
5 |
Caleigh |
Connelly |
Hobart91@hotmail.com |
2021-07-25T00:00:00.000Z |
6 |
Whitney |
Jenkins |
Kenya_Legros17@yahoo.com |
2022-02-07T00:00:00.000Z |
… |
… |
… |
… |
… |
99 |
Julianne |
Hermiston |
Maximillian28@yahoo.com |
2021-12-30T00:00:00.000Z |
View on DB Fiddle
Такой запрос вернет пользователей, чьи фамилии имеют любые окончания кроме "man".
Выводы
В этом уроке мы рассмотрели оператор LIKE для фильтрации данных по подстроке. Теперь вы сможете создать шаблон поиска с символом %, который заменяет любое количество любых символов. Таким образом удобно искать данные, соответствующие шаблону — например, схожие фамилии пользователей.
Остались вопросы? Задайте их в разделе «Обсуждение»
Вам ответят команда поддержки Хекслета или другие студенты
