Regular Expressions

Regular Expressions

Regular Expressions with REGEXP_LIKE and Its Companions

Since MySQL 8.0 524 regular expressions run on the Unicode-aware ICU library. REGEXP and RLIKE are synonyms for REGEXP_LIKE(), and three companions return where (REGEXP_INSTR), what (REGEXP_SUBSTR) or a rewritten string (REGEXP_REPLACE). A regex matches anywhere unless anchored with ^ and $, and a match type of 'c' (case-sensitive), 'i', 'm' (multiline) or 'n' overrides the collation.

Extracting, replacing and validating with the REGEXP functionsSQL

SELECT sku, REGEXP_SUBSTR(sku, '[A-Z]{2,3}', 1, 2) AS topic,
       REGEXP_INSTR(title, ' ') AS first_space, REGEXP_REPLACE(title, '\\s+', '_') AS snake
FROM products WHERE sku REGEXP '^BK-(SQL|LNX)-';
SELECT REGEXP_LIKE('CamelCase', 'camelcase') AS ci,
       REGEXP_LIKE('CamelCase', 'camelcase', 'c') AS cs,
       REGEXP_SUBSTR('Order 9 of 2026', '\\d+', 1, 2) AS second_num,
       REGEXP_LIKE('1+2', '1\+2') AS one_slash, REGEXP_LIKE('1+2', '1\\+2') AS two_slash;
CREATE TABLE sku_rule (sku VARCHAR(20)
  CHECK (REGEXP_LIKE(sku, '^[A-Z]{2}-[A-Z]{2,3}-[0-9]{2}$', 'c')));
INSERT INTO sku_rule VALUES ('BK-PHP-01'), ('bk-php-1');

Output

+-----------+-------+-------------+---------------------------+
| sku       | topic | first_space | snake                     |
+-----------+-------+-------------+---------------------------+
| BK-SQL-01 | SQL   |           4 | SQL_Queries_That_Scale    |
| BK-SQL-02 | SQL   |           9 | Indexing_Deep_Dive        |
| BK-LNX-01 | LNX   |           4 | The_Linux_Server_Handbook |
+-----------+-------+-------------+---------------------------+
+----+----+------------+-----------+-----------+
| ci | cs | second_num | one_slash | two_slash |
+----+----+------------+-----------+-----------+
|  1 |  0 | 2026       |         0 |         1 |
+----+----+------------+-----------+-----------+
ERROR 3819 (HY000): Check constraint 'sku_rule_chk_1' is violated.

After the pattern come start position, occurrence and match type, so topic is the second letter group. Double every backslash: the string parser eats one, so '1\+2' reaches ICU as 1+2, "one or more 1s, then 2". regexp_time_limit and regexp_stack_limit make a runaway pattern fail with an error instead of hanging the connection.

MENU