MySQL REGEXP: Regular Expression Matching and REGEXP

When LIKE is not enough, use a regular expression: a compact pattern language for text. MySQL 8 uses the ICU library, which supports full Unicode and modern syntax.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

REGEXP and REGEXP_LIKE

value REGEXP pattern is true when the pattern matches anywhere in the value. REGEXP_LIKE(value, pattern) is the same thing as a function.

query.sql

SQL

SELECT name FROM products WHERE name REGEXP 'pen|book' ORDER BY name;

Output

OUTPUT

+----------+
| name     |
+----------+
| Gel Pen  |
| Notebook |
+----------+
2 rows in set

The | means “or”. The match is not anchored, so it can be anywhere.

The building blocks

Pattern	Meaning
`^`	Start of the text
`$`	End of the text
`.`	Any single character
`[abc]`	One of a, b or c
`[a-z]`	One lowercase letter
`[^0-9]`	Any character except a digit
`a*` / `a+` / `a?`	Zero or more / one or more / zero or one `a`
`a{2,4}`	Two to four `a`
`(ab	cd)`	`ab` or `cd`
`\\d`, `\\w`, `\\s`	Digit, word character, whitespace

In an SQL string, write a backslash twice ('\\d'), because MySQL reads the backslash escape first.

query.sql

SQL

1
2
3

SELECT name FROM products WHERE name REGEXP '^[A-G]' ORDER BY name;
SELECT name FROM products WHERE name REGEXP 'k$' ORDER BY name;
SELECT name FROM products WHERE name REGEXP '^[A-Za-z]+ [A-Za-z]+$' ORDER BY name;

Output

OUTPUT

+-----------+
| name      |
+-----------+
| Backpack  |
| Desk Lamp |
| Gel Pen   |
+-----------+
3 rows in set

+----------+
| name     |
+----------+
| Backpack |
| Notebook |
+----------+
2 rows in set

+---------------+
| name          |
+---------------+
| Desk Lamp     |
| Gel Pen       |
| Laptop Sleeve |
| Marker Set    |
| Sticky Notes  |
| Water Bottle  |
+---------------+
6 rows in set

The last pattern finds names made of exactly two words.

Validating data

A common use is checking the format of a value:

query.sql

SQL

CREATE TABLE phones (id INT AUTO_INCREMENT PRIMARY KEY, number VARCHAR(20));
INSERT INTO phones (number) VALUES ('+91 98450 12345'), ('98450-12345'), ('12345'), ('call me');

SELECT number, number REGEXP '^[+]?[0-9 -]{10,15}$' AS looks_valid FROM phones ORDER BY id;

Output

OUTPUT

+-----------------+-------------+
| number          | looks_valid |
+-----------------+-------------+
| +91 98450 12345 |           1 |
| 98450-12345     |           1 |
| 12345           |           0 |
| call me         |           0 |
+-----------------+-------------+
4 rows in set

It checks the shape, not the truth. A real validation also needs your application’s rules.

Case sensitivity

REGEXP follows the collation, so it is case-insensitive with the default one. Control it with the optional match type argument of REGEXP_LIKE: 'c' for case-sensitive and 'i' for case-insensitive.

query.sql

SQL

1
2
3

SELECT REGEXP_LIKE('Notebook', 'note')      AS default_match,
       REGEXP_LIKE('Notebook', 'note', 'c') AS case_sensitive,
       REGEXP_LIKE('Notebook', 'note', 'i') AS ignore_case;

Output

OUTPUT

+---------------+----------------+-------------+
| default_match | case_sensitive | ignore_case |
+---------------+----------------+-------------+
|             1 |              0 |           1 |
+---------------+----------------+-------------+
1 row in set

Extract and replace (MySQL 8.0)

query.sql

SQL

SELECT REGEXP_REPLACE('Order #1234 shipped', '[0-9]+', 'N')        AS replaced,
       REGEXP_SUBSTR('Order #1234 shipped', '[0-9]+')               AS extracted,
       REGEXP_INSTR('Order #1234 shipped', '[0-9]+')                AS position,
       REGEXP_REPLACE('a   lot   of   spaces', '\\s+', ' ')        AS cleaned;

Output

OUTPUT

+------------------+-----------+----------+-----------------+
| replaced         | extracted | position | cleaned         |
+------------------+-----------+----------+-----------------+
| Order #N shipped | 1234      |        8 | a lot of spaces |
+------------------+-----------+----------+-----------------+
1 row in set

REGEXP_REPLACE is excellent for cleaning data: collapsing spaces, removing non-digits from phone numbers, and so on.

query.sql

SQL

SELECT REGEXP_REPLACE('+91 (98450) 12-345', '[^0-9]', '') AS digits_only;

Output

OUTPUT

+--------------+
| digits_only  |
+--------------+
| 919845012345 |
+--------------+
1 row in set

Performance

A regular expression cannot use an index, and it is slower than LIKE. It must test every row. Use it:

On small tables or after other conditions have already cut the rows down.
For validation and cleaning.
Not as the main filter on a large table’s hot path.

Versions

MySQL 8.0.4 and later use ICU. Older versions (5.7) used Henry Spencer’s library, with different syntax for word boundaries ([[:<:]]) and no REGEXP_REPLACE.
MariaDB uses PCRE, so a few patterns behave differently.
If you move code between servers, test every pattern.

lightbulb

Test patterns first

Try a pattern on a few literal values with SELECT 'text' REGEXP 'pattern'; before you apply it to a table.