MySQL - Regular Expressions

MySQL: Advanced Pattern Matching with Regular Expressions

Regular expressions (regex) provide a powerful and flexible way to search for complex patterns in text data, going far beyond the simple wildcards of the LIKE operator. MySQL 8+ uses the International Components for Unicode (ICU) library, which provides a modern, performant, and Unicode-aware regex engine.

The REGEXP_LIKE() Function

The primary function for regex matching is REGEXP_LIKE(). It returns 1 (true) if the input string matches the pattern and 0 (false) otherwise. The legacy operators REGEXP and RLIKE are now synonyms for REGEXP_LIKE().

Syntax

REGEXP_LIKE(expression, pattern [, match_type])

-- Or using the operator syntax:
expression REGEXP pattern

The optional match_type string allows you to modify matching behavior (e.g., 'i' for case-insensitive matching).

Common Regex Patterns

Here are some of the most frequently used metacharacters and constructs in regex patterns:

Pattern	Description
^	Anchors the match to the beginning of the string.
$	Anchors the match to the end of the string.
.	Matches any single character (except newline).
[abc]	Matches any one character in the set (a, b, or c).
[^abc]	Matches any one character NOT in the set.
p1|p2	Alternation: matches either pattern p1 or pattern p2.
*	Matches the preceding element zero or more times.
+	Matches the preceding element one or more times.
?	Matches the preceding element zero or one time.
{n}	Matches the preceding element exactly n times.
{n,m}	Matches the preceding element from n to m times.
\d	Matches any digit. Equivalent to [0-9].
\s	Matches any whitespace character.
\w	Matches any word character (alphanumeric + underscore).

Examples

Let’s use a products table to demonstrate these patterns.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(20) NOT NULL,
    product_name VARCHAR(100) NOT NULL
);

INSERT INTO products (product_code, product_name) VALUES
('SKU-001A', 'Wireless Mouse'),
('SKU-002B', '104-Key Keyboard'),
('HW-003C', 'Webcam Pro'),
('HW-004D', 'Monitor 24 inch'),
('ACC-005', 'Mouse Pad');

Find products whose code starts with ‘SKU’:

SELECT * FROM products WHERE REGEXP_LIKE(product_code, '^SKU');

Find products whose name contains a number:

SELECT * FROM products WHERE product_name REGEXP '\\d'; -- Double backslash is needed to escape in SQL strings

Find products whose name is exactly ‘Webcam Pro’ or ‘Wireless Mouse’ (case-sensitive):

SELECT * FROM products WHERE REGEXP_LIKE(product_name, '^(Webcam Pro|Wireless Mouse)$');

Find products whose code ends with a letter (case-insensitive match):

SELECT * FROM products WHERE REGEXP_LIKE(product_code, '[a-z]$', 'i');

Advanced Regex Functions

Beyond simple matching, MySQL offers functions to extract, replace, and locate substrings using regex.

REGEXP_SUBSTR() - Extract Substring

Extracts the part of a string that matches the regex pattern.

-- Extract the numeric part of the product name
SELECT product_name, REGEXP_SUBSTR(product_name, '\\d+') AS number_in_name
FROM products;

REGEXP_REPLACE() - Replace Substring

Replaces occurrences of a regex pattern with another string.

-- Standardize product codes by replacing '-' with an empty string
SELECT product_code, REGEXP_REPLACE(product_code, '-', '') AS standardized_code
FROM products;

REGEXP_INSTR() - Find Substring Position

Returns the starting position (1-based index) of the substring that matches the regex pattern.

-- Find the position of the first number in the product name
SELECT product_name, REGEXP_INSTR(product_name, '\\d') AS first_digit_position
FROM products
WHERE REGEXP_LIKE(product_name, '\\d');

Performance and Best Practices

• Indexing: Standard database indexes (like B-Tree) are generally not effective for regex searches, especially for patterns that are not anchored to the beginning of the string (^). This can lead to full table scans and slow performance on large tables.
• LIKE vs. REGEXP: For simple prefix searches ('word%'), LIKE is significantly faster as it can use an index. Use REGEXP only when the pattern complexity requires it.
• Full-Text Search: For searching natural language words and phrases within large blocks of text, consider using MySQL’s Full-Text Search capabilities, which are specifically designed and optimized for that purpose.
• Complexity: Be mindful of writing overly complex or inefficient regex patterns (e.g., with excessive backtracking), as they can consume significant CPU resources.

Table of Regular Expression Functions and Operators

S. No	Function or Operator	Description
1	REGEXP_LIKE()	The primary function to check if a string matches a regular expression. Returns 1 or 0.
2	REGEXP / RLIKE	Operators that are synonyms for REGEXP_LIKE(). Often used in WHERE clauses.
3	NOT REGEXP	Negation of REGEXP_LIKE(). Returns 1 if the pattern does not match, 0 otherwise.
4	REGEXP_INSTR()	Returns the 1-based starting index of the substring that matches the regular expression.
5	REGEXP_REPLACE()	Replaces substrings matching the regular expression with a new string.
6	REGEXP_SUBSTR()	Returns the substring that matches the regular expression.