Оператор REGEXP

Оператор REGEXP (или его синоним RLIKE) в SQL используется для поиска и обработки строковых данных с помощью регулярных выражений. Регулярные выражения предоставляют мощные возможности для сложных шаблонов поиска, которые трудно реализовать с помощью оператора LIKE.

Когда использовать REGEXP вместо LIKE?

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

Синтаксис регулярных выражений

MySQL
... WHERE table_field REGEXP 'pattern';

Где pattern — это регулярное выражение, задающее шаблон поиска.

Важные нюансы

1. Регистронезависимость

   По умолчанию регулярные выражения в MySQL не чувствительны к регистру. Например, выражение REGEXP 'abc' найдёт строку и abc, и Abc, и ABC.

2. Специальные символы

   Некоторые символы имеют особое значение в регулярных выражениях и требуют экранирования (например, ., *, +, ?, [, ], (, ), {, }, |, \). Для экранирования используется обратная косая черта \.

Специальные символы и структуры

Примеры с объяснением

• Получим всех пользователей, чьи имена начинаются на «John»:

  MySQL
  SELECT * FROM Users WHERE name REGEXP '^John'
  

  id	name	email	email_verified_at	password	phone_number
  18	John Travolta	[email protected]	2016-11-19T12:30:43.000Z	fzjhl0v82o0amalr8649	+1 202 555 0176
  28	Johnny Depp	[email protected]	2017-05-26T01:19:06.000Z	qpp6hbnae42cdhmxlk4j	+7 401 195 7363

  Это выражение ищет строки, начинающиеся с «John». Символ ^ указывает на начало строки.

• Выведем все школьные предметы, название которых оканчивается на букву «e» или «y»:

  MySQL
  SELECT * FROM  Subject WHERE name REGEXP '[ey]$'
  

  id	name
  2	Russian language
  3	Literature
  5	Chemistry
  6	Geography
  7	History
  8	Biology
  9	English language
  11	Physical Culture
  13	Technology

  В этом примере, [ey] определяет список возможных значений для паттерна $, определяющего на что должна заканчиваться строка.

• Найдём всех пользователей, чей адрес электронной почты oканчивается на «@outlook.com» или на «@icloud.com»:

  MySQL
  SELECT * FROM Users WHERE email REGEXP '@(outlook.com|icloud.com)$'
  

  id	name	email	email_verified_at	password	phone_number
  7	Samuel L. Jackson	[email protected]	2018-07-19T11:16:13.000Z	i6yvht95527z3idgqx9y	+1 202 555 0162
  13	Steve Martin	[email protected]	2016-07-29T04:25:00.000Z	w76yphg3kvzg77ilmxfs	+1 202 555 0138
  29	Pierce Brosnan	[email protected]	2019-03-08T01:56:00.000Z	lqiwecclne9rv8woo2go	+7 401 749 3620
  30	Sean Connery	[email protected]	2016-05-21T00:45:17.000Z	lyh4jkdxkvtvulvqi5db	+7 401 511 6783
  31	Bruce Willis	[email protected]	2016-12-08T20:18:59.000Z	0ofa2khvnptiackbssv0	+375 154 771 3462

  Здесь также используется $ для обозначения конца строки и | для указания нескольких вариантов.

• Найдём всех пользователей, чей номер телефона не содержит цифр «2» и «8»:

  MySQL
  SELECT * FROM Users WHERE phone_number REGEXP '^[^28]*$'
  

  id	name	email	email_verified_at	password	phone_number
  27	Brad Pitt	[email protected]	2017-02-11T05:45:15.000Z	829j2ygocn8btzae49kv	+7 401 741 3797
  28	Johnny Depp	[email protected]	2017-05-26T01:19:06.000Z	qpp6hbnae42cdhmxlk4j	+7 401 195 7363

  В этом примере символ [^28] обозначает любой символ, кроме «2» и «8», а * означает любое количество таких символов. Символы ^ и $ указывают на начало и конец строки соответственно, гарантируя, что вся строка соответствует шаблону.

• Найдём всех пользователей, чей номер телефона начинается на «+7»

  MySQL
  SELECT name, phone_number FROM Users WHERE phone_number REGEXP '^\\+7'
  

  name	phone_number
  Hideo Kojima	+7 401 452 0052
  ClINT Eastwood	+7 401 722 0912
  Brad Pitt	+7 401 741 3797
  Johnny Depp	+7 401 195 7363
  Pierce Brosnan	+7 401 749 3620
  Sean Connery	+7 401 511 6783

  В этом примере ^ означает начало строки. То есть, мы ищем строки, которые начинаются с определённого шаблона.

  Поскольку + является специальным символом в регулярных выражениях, его нужно экранировать двойным обратным слэшем (\\), чтобы он воспринимался как обычный символ +. В результате, \\+ соответствует знаку + в строке.