REGEXP Pattern Matching

LIKE can answer "does the name start with Lap?", but not "does the phone number contain exactly ten digits starting with 6, 7, 8 or 9?" or "is this a valid six-digit PIN code?". For questions about the shape of text, you need a regular expression (regex for short): a small pattern language for describing text.

In this lesson you will use regular expressions in MySQL to search, validate, extract and clean text. MySQL 8 has a full set of regex functions. The browser's SQLite does not, so apart from the first LIKE example, the examples show MySQL's output.

When LIKE is not enough

LIKE has just two wildcards: % for any run of characters and _ for exactly one character (see LIKE & Wildcards). Suppose you want every product whose name starts with a digit. With LIKE, you must list the digits yourself:

SELECT product_name, category
FROM products
WHERE product_name LIKE '1%' OR product_name LIKE '2%' OR product_name LIKE '6%';

product_name     | category
-----------------+---------
27-inch Monitor  | Displays
1TB External SSD | Storage
64GB Pen Drive   | Storage
(3 rows)

This works only because you already looked at the data. A new product called "4K Webcam" would be missed. A regular expression says what you really mean, "starts with any digit", in one short pattern:

SELECT product_name, category
FROM products
WHERE product_name REGEXP '^[0-9]';

The result is the same three rows. ^ means "at the start" and [0-9] means "any one digit".

REGEXP, RLIKE and REGEXP_LIKE

MySQL gives you three ways to write the same test. expr REGEXP pattern and expr RLIKE pattern are operators; REGEXP_LIKE(expr, pattern) is the function form. All three return 1 if the pattern is found, 0 if not, and NULL if either side is NULL. NOT REGEXP reverses the test.

There is one big difference from LIKE. A LIKE pattern must match the whole value, which is why you write '%Pen%'. A regex matches if the pattern appears anywhere, unless you anchor it with ^ (start) or $ (end).

SELECT product_name,
       product_name REGEXP '[0-9]+(GB|TB)' AS has_capacity,
       product_name REGEXP '^[A-M]' AS a_to_m,
       product_name REGEXP 'hub|stand|mouse' AS desk_item
FROM products
WHERE product_id IN (1, 2, 5, 9, 10, 12);

product_name        | has_capacity | a_to_m | desk_item
--------------------+--------------+--------+----------
Wireless Mouse      | 0            | 0      | 1
Mechanical Keyboard | 0            | 1      | 0
Laptop Stand        | 0            | 1      | 1
1TB External SSD    | 1            | 0      | 0
64GB Pen Drive      | 1            | 0      | 0
Graphics Tablet     | 0            | 1      | 0
(6 rows)

Notice that 'hub|stand|mouse' matched Mouse and Stand with capital letters. Regex matching follows the column's collation, and the default utf8mb4_0900_ai_ci is case-insensitive. To force an exact-case match, pass the match type 'c': REGEXP_LIKE(product_name, '^usb', 'c') returns 0 for USB-C Hub. The match type 'i' forces case-insensitive matching.

Building patterns

Most patterns are built from a handful of pieces:

The double backslash is not a typo. Inside a MySQL string, \ is an escape character, so to send one backslash to the regex engine you type two. The same applies when you want a special character literally: '1\\+1' matches the text 1+1. Writing [.] or [+] avoids backslashes entirely.

Extracting and replacing text

Four functions arrived in MySQL 8.0 for working inside the text:

• REGEXP_SUBSTR(expr, pattern) returns the part that matched.
• REGEXP_INSTR(expr, pattern) returns the position where the match starts (0 if none).
• REGEXP_REPLACE(expr, pattern, replacement) replaces every match. In the replacement, $1 means "whatever the first bracketed group matched".
• REGEXP_LIKE(expr, pattern) is the yes-or-no test you already saw.

SELECT email,
       REGEXP_SUBSTR(email, '[^@]+$') AS domain,
       REGEXP_INSTR(email, '[.]') AS dot_at,
       REGEXP_REPLACE(email, '^([a-z])[a-z]*', '$1***') AS masked
FROM customers
WHERE customer_id IN (2, 4, 5, 11);

email                   | domain      | dot_at | masked
------------------------+-------------+--------+-----------------------
priya.verma@example.com | example.com | 6      | p***.verma@example.com
NULL                    | NULL        | NULL   | NULL
kabir.singh@example.com | example.com | 6      | k***.singh@example.com
farhan.khan@example.com | example.com | 7      | f***.khan@example.com
(4 rows)

[^@]+$ means "one or more characters that are not @, up to the end", which is the domain. The masking pattern captures the first letter in a group and keeps it with $1, replacing the rest of the first name with stars. That is useful for screens where support staff should not see full email addresses. Ananya has no email, so every function returns NULL.

All three extra functions take optional arguments for the start position and which occurrence you want; for example, REGEXP_SUBSTR('64GB and 1TB', '[0-9]+', 1, 2) returns 1, the second run of digits.

A real task: cleaning phone numbers

Phone numbers typed into web forms arrive in every style. The shop wants clean ten-digit mobile numbers. This example strips everything except digits, removes a leading 91 country code from 12-digit numbers, and then checks the result against the shape of an Indian mobile number:

CREATE TABLE phone_list (contact_id INT PRIMARY KEY, phone VARCHAR(30) NOT NULL);
INSERT INTO phone_list VALUES
  (1, '98120-45678'), (2, '+91 98765 43210'), (3, '0124 2345678'), (4, '91234');

WITH cleaned AS (
  SELECT phone,
         REGEXP_REPLACE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^91([0-9]{10})$', '$1') AS mobile
  FROM phone_list
)
SELECT phone, mobile, REGEXP_LIKE(mobile, '^[6-9][0-9]{9}$') AS valid_mobile
FROM cleaned;

phone           | mobile      | valid_mobile
----------------+-------------+-------------
98120-45678     | 9812045678  | 1
+91 98765 43210 | 9876543210  | 1
0124 2345678    | 01242345678 | 0
91234           | 91234       | 0
(4 rows)

The inner REGEXP_REPLACE turns +91 98765 43210 into 919876543210. The outer one matches only a 91 followed by exactly ten more digits, and replaces the whole thing with those ten digits. The landline and the short number fail the final check, so you can list them for someone to fix by hand.

You can also make the database refuse bad data. Since MySQL 8.0.16, CHECK constraints are enforced, and they may use regex functions:

CREATE TABLE stores (
  store_id INT PRIMARY KEY,
  city VARCHAR(40) NOT NULL,
  pin_code CHAR(6) NOT NULL,
  CONSTRAINT chk_pin CHECK (REGEXP_LIKE(pin_code, '^[1-9][0-9]{5}$'))
);

INSERT INTO stores VALUES (1, 'Rohtak', '124001');   -- accepted
INSERT INTO stores VALUES (2, 'Kochi', '68201');     -- only five digits

ERROR 3819 (HY000): Check constraint 'chk_pin' is violated.

Speed and older versions

A regex test cannot use an ordinary index, because MySQL has to run the pattern against every value. On a large table, filter by an indexed column first and apply the regex to the remaining rows. If you are searching for words in long text, such as product descriptions, use Full-text Search instead. MySQL also stops patterns that run too long; the regexp_time_limit and regexp_stack_limit system variables control the limits.

Common mistakes

• Forgetting anchors. REGEXP '[0-9]{6}' also matches a seven-digit value, because it finds six digits somewhere. Use '^[0-9]{6}$' to test the whole value.
• Single backslashes. '\d' reaches the regex engine as d. Write '\\d', or use [0-9].
• Using LIKE syntax. % and _ are not regex wildcards. In a regex, use .* and ..
• Regex where = or LIKE is enough. REGEXP '^Rohtak$' is just a slow = 'Rohtak'.

Key points

• REGEXP, RLIKE and REGEXP_LIKE() test whether a pattern appears anywhere in the text; anchor with ^ and $.
• Character sets [0-9], repeats + * ? {n}, alternatives | and groups ( ) build most patterns.
• Double every backslash inside MySQL strings: '\\d', '\\b'.
• REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_REPLACE extract, locate and replace; $1 reuses a group.
• Matching is case-insensitive with the default collation; pass 'c' for exact case.
• Regex cannot use normal indexes; MySQL 8 uses ICU, and old [[:<:]] markers no longer work.

SELECT first_name, last_name
FROM customers
WHERE last_name REGEXP '[aeiou]$';

SELECT product_name
FROM products
WHERE REGEXP_LIKE(product_name, '\\b[A-Z]{2}(\\s|$)', 'c');

SELECT email, REGEXP_REPLACE(email, '@.+$', '@shop.in') AS new_email
FROM customers
WHERE email IS NOT NULL;