Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Фильтрация строк Основы SQL

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

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

id

first_name

last_name

email

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

email

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

email

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

email

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

email

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

email

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 для фильтрации данных по подстроке. Теперь вы сможете создать шаблон поиска с символом %, который заменяет любое количество любых символов. Таким образом удобно искать данные, соответствующие шаблону — например, схожие фамилии пользователей.


Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы
профессия
Обучитесь разработке бэкенда сайтов и веб-приложений — серверной части, которая отвечает за логику и базы данных
10 месяцев
с нуля
Старт 21 ноября
профессия
Выполняйте ручное тестирование веб-приложений, находите ошибки в продукте. Узнайте все о тест-дизайне.
4 месяца
с нуля
Старт 21 ноября
профессия
Научитесь разработке веб-приложений, сайтов и программного обеспечения на языке Java, программируйте и используйте структуры данных
10 месяцев
с нуля
Старт 21 ноября
профессия
новый
Собирайте, анализируйте и интерпретируйте данные, улучшайте бизнес-процессы и продукт компании. Обучитесь работе с библиотеками Python
9 месяцев
с нуля
Старт 21 ноября
профессия
Занимайтесь созданием сайтов, веб-приложений, сервисов и их интеграцией с внутренними бизнес-системами на бекенд-языке PHP
10 месяцев
с нуля
Старт 21 ноября
профессия
Обучитесь разработке визуальной части сайта — фронтенда, а также реализации серверной — бэкенда. Освойте HTML, CSS, JavaScript
16 месяцев
с нуля
Старт 21 ноября
профессия
Разработка бэкенд-компонентов для веб-приложений
10 месяцев
с нуля
Старт 21 ноября
профессия
новый
Организовывайте процесс автоматизации тестирования на проекте, обучитесь языку программирования JavaScript, начните управлять процессом тестирования
8 месяцев
c опытом
Старт 21 ноября

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»
Изображение Тото

Задавайте вопросы, если хотите обсудить теорию или упражнения. Команда поддержки Хекслета и опытные участники сообщества помогут найти ответы и решить задачу