MySQL REGEXP

Summary: in this tutorial, you will learn how to use the MySQL REGEXP operator to determine if a string matches a regular expression.

Introduction to MySQL REGEXP operator

The REGEXP operator allows you to check whether a string matches a regular expression.

Here’s the basic syntax of the REGEXP operator:

expression REGEXP patternCode language: SQL (Structured Query Language) (sql)

In this syntax, the REGEXP operator returns 1 if the expression matches the pattern or 0 otherwise.

If the expression or pattern is NULL, the REGEXP operator returns NULL.

Note that the REGEXP is a synonym for the REGEXP_LIKE() function.

MySQL REGEXP operator examples

We’ll take some examples of using the REGEXP operator.

1) Simple REGEXP operator examples

The following example uses the REGEXP operator to check if a string contains any digits:

SELECT 'MySQL 8.0' REGEXP '\\d+';Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------+
| 'MySQL 8.0' REGEXP '\\d+' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, the pattern '\\d+' matches one or more digits. Since the string MySQL 8.0 contains the digits 8 and 0, the REGEXP returns 1.

The following example checks if the string 'MySQL 8.0' has a version that includes a digit, a dot, and a digit:

SELECT 
  'MySQL 8.0' REGEXP '\\d\.\\d';Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------+
| 'MySQL 8.0' REGEXP '\\d\.\\d' |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The pattern '\\d\.\\d' matches a digit, followed by the character (.), and then followed by another digit.

If you use the input string as 'MySQL 8', the REGEXP operator will return 0 as shown in the following query:

SELECT 
  'MySQL 8' REGEXP '\\d\.\\d';Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------------+
| 'MySQL 8' REGEXP '\\d\.\\d' |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using MySQL REGEXP operator with table data

We’ll use the products table from the sample database for the demo:

The following query uses the REGEXP operator to find the products whose names contain the number 193 followed by any single digit e.g.,1930:

SELECT 
  productName 
FROM 
  products 
WHERE 
  productName REGEXP '193\\d';Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------------------------+
| productName                               |
+-------------------------------------------+
| 1937 Lincoln Berline                      |
| 1936 Mercedes-Benz 500K Special Roadster  |
| 1932 Model A Ford J-Coupe                 |
| 1936 Harley Davidson El Knucklehead       |
| 1934 Ford V8 Coupe                        |
| 1932 Alfa Romeo 8C2300 Spider Sport       |
| 1939 Cadillac Limousine                   |
| 1939 Chevrolet Deluxe Coupe               |
| 1938 Cadillac V-16 Presidential Limousine |
| 1937 Horch 930V Limousine                 |
| 1936 Mercedes Benz 500k Roadster          |
| 1936 Chrysler Airflow                     |
| 1930 Buick Marquette Phaeton              |
+-------------------------------------------+
13 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the REGEXP operator to determine if a string matches a regular expression.