MySQL REGEXP_LIKE() Function

Summary: in this tutorial, you will learn how to use the MySQL REGEXP_LIKE() function to check if a string matches a regular expression.

Introduction to MySQL REGEXP_LIKE function

The REGEXP_LIKE() function returns 1 if a string matches a regular expression or 0 otherwise.

Here’s the syntax of the REGEXP_LIKE() function:

REGEX_LIKE(string, pattern, match_type)Code language: JavaScript (javascript)

In this syntax:

• string: This is the input string you want to check if it matches a pattern.
• pattern: This is the regular expression that you want to match.
• match_type is a string that includes one or more option that specifies how the function will perform matching. For example, c is case-sensitive matching whereas i is case-insensitive matching.

If the string or pattern is NULL, the REGEXP_LIKE() function returns NULL.

MySQL REGEXP_LIKE() function examples

Let’s take some examples of using the REGEXP_LIKE() function.

1) Simple REGEXP_LIKE() function examples

The following example uses the REGEXP_LIKE() function to check if the string MySQL 8.0 matches with the regular expression "\\d+\\.\\d+":

SELECT REGEXP_LIKE("MySQL 8.0","\\d+\\.\\d+");Code language: JavaScript (javascript)

Output:

+----------------------------------------+
| REGEXP_LIKE("MySQL 8.0","\\d+\\.\\d+") |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

In this example, the pattern "\\d+\\.\\d+" matches one or more digits, followed by a character (.), and followed by one or more digits.

Since the input string has the substring 8.0 that matches the pattern, the REGEXP_LIKE() function returns 1.

The following example uses the REGEXP_LIKE() function and returns 0 because the input string doesn’t match the regular expression:

SELECT REGEXP_LIKE("MySQL 8","\\d+\\.\\d+");Code language: JavaScript (javascript)

Output:

+--------------------------------------+
| REGEXP_LIKE("MySQL 8","\\d+\\.\\d+") |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

2) Using REGEXP_LIKE() function with the table data example

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

The following example uses the REGEXP_LIKE() function to get the product names that start with 4 digits "^\\d{4}":

SELECT 
  productName 
FROM 
  products 
WHERE 
  REGEXP_LIKE(productName, "^\\d{4}");Code language: JavaScript (javascript)

Output:

+---------------------------------------------+
| productName                                 |
+---------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper       |
| 1952 Alpine Renault 1300                    |
| 1996 Moto Guzzi 1100i                       |
| 2003 Harley-Davidson Eagle Drag Bike        |
| 1972 Alfa Romeo GTA                         |
...Code language: JavaScript (javascript)

In this example, we use the REGEXP_LIKE() function in the WHERE clause. If the product name matches the regular expression, it returns 1, the query includes the product in the result set.

3) Using REGEXP_LIKE function to validate data example

Suppose you have a table called contacts with three columns: id, name, and email:

CREATE TABLE contacts(
  id INT AUTO_INCREMENT, 
  name VARCHAR(255) NOT NULL, 
  email VARCHAR(300) NOT NULL, 
  PRIMARY KEY(id)
);Code language: JavaScript (javascript)

Before inserting or updating a value into the email column, you may want to check if the email format is valid.

To do that you can use the REGEXP_LIKE with a CHECK constraint. The CHECK constraint will execute the REGEXP_LIKE() function whenever you insert a value into the email column or update a value from the email column.

The following statement adds a CHECK constraint to the email column of the contacts table:

ALTER TABLE 
  contacts 
ADD 
  CONSTRAINT email_validation CHECK (
     REGEXP_LIKE(email, "^\\S+@\\S+\\.\\S+$") = 1
  );Code language: JavaScript (javascript)

Notice that for brevity, we use a simple regular expression to validate email in most cases. For a comprehensive one, you can use the regex following regex that matches email addresses based on the RFC2822 standard:

/[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?/gCode language: JavaScript (javascript)

The following statement inserts a new row with a valid email format into the contacts table:

INSERT INTO contacts(name, email) 
VALUES("John Doe", "[email protected]");Code language: JavaScript (javascript)

It works as expected.

However, if you insert a new contact with an invalid email address, the CHECK constraint will be violated, which will result in the rejection of the insertion:

INSERT INTO contacts(name, email) 
VALUES("Jane Doe", "jane.doe@mysqltutorial");Code language: JavaScript (javascript)

Output:

ERROR 3819 (HY000): Check constraint 'email_validation' is violated.Code language: JavaScript (javascript)

Similarly, if you update an existing email to an invalid format, the change will not be applied:

UPDATE 
  contacts 
SET 
  email = 'invalid email' 
WHERE 
  id = 1;Code language: JavaScript (javascript)

Summary

• Use the REGEXP_LIKE() function to check if an input string matches with a regular expression.