REGEXP Operator

The REGEXP operator (or its synonym RLIKE) in SQL is used for searching and manipulating string data using regular expressions. Regular expressions provide powerful capabilities for complex search patterns that are difficult to implement with the LIKE operator.

When to use REGEXP instead of LIKE?

The LIKE operator is convenient for simple search patterns, such as finding strings that start or end with a certain set of characters or contain certain substrings. However, if a more complex and flexible search is required, such as searching by multiple conditions or using special characters and ranges, the REGEXP operator becomes an indispensable tool.

Regular expression syntax

MySQL
... WHERE table_field REGEXP 'pattern';

Where pattern is the regular expression that defines the search pattern.

Important Considerations

1. Case insensitive

   By default, regular expressions in MySQL are not case-sensitive.
   For example, the expression REGEXP 'abc' will match the string abc, Abc, and ABC.

2. Special characters

   Some characters have special meanings in regular expressions and require escaping (e.g., ., *, +, ?, [, ], (, ), {, }, |, \). Use a backslash \ for escaping.

Special characters and constructs

Examples with explanation

• Get all users whose names start with "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

  This expression searches for strings starting with "John". The ^ symbol indicates the start of the string.

• Display all school subjects whose names end with the letter “e” or “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

  In this example, [ey] defines a list of possible values for the pattern $, which defines what the string should end with.

• Find all users whose email addresses end with “@outlook.com” or “@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

  Here, $ is used to indicate the end of the string and | is used to specify multiple options.

• Find all users whose phone numbers do not contain the digits “2” and “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

  In this example, the symbol [^28] represents any character except “2” and “8”, and * means any number of such characters. The ^ and $ symbols indicate the start and end of the string respectively, ensuring that the entire string matches the pattern.

• Find all users whose phone number starts with «+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

  In this example, ^ denotes the beginning of a string. This means we are looking for strings that start with a specific pattern.

  Since + is a special character in regular expressions, it needs to be escaped with a double backslash (\\) so that it is treated as the literal + character. As a result, \\+ matches the + sign in the string.