How to Use REGEXP in MySQL: Syntax and Examples

In MySQL, you can use the REGEXP operator in a WHERE clause to match strings against a regular expression pattern. The syntax is column_name REGEXP 'pattern', which returns rows where the column matches the pattern.

Syntax

The basic syntax for using regular expressions in MySQL is:

• column_name REGEXP 'pattern': Checks if the column's value matches the regular expression pattern.
• NOT REGEXP: Checks if the column's value does not match the pattern.
• Patterns are case-insensitive by default.

sql

SELECT * FROM table_name WHERE column_name REGEXP 'pattern';

Example

This example shows how to select rows where the name column contains names starting with 'J' followed by any characters.

sql

CREATE TABLE users (id INT, name VARCHAR(50));
INSERT INTO users VALUES (1, 'John'), (2, 'Jane'), (3, 'Alice'), (4, 'Bob');

SELECT * FROM users WHERE name REGEXP '^J';

Output

id | name
---|------
1  | John
2  | Jane

Common Pitfalls

Common mistakes when using REGEXP in MySQL include:

• Using case-sensitive patterns without knowing MySQL REGEXP is case-insensitive by default.
• Forgetting to escape special characters like \. in patterns.
• Using LIKE when a regular expression is needed for complex patterns.

Example of a wrong and right pattern:

sql

SELECT * FROM users WHERE name REGEXP 'J.'; -- Matches 'Jo', 'Ja', etc.

-- Wrong: forgetting to escape dot if literal dot needed
SELECT * FROM users WHERE name REGEXP 'J.'; -- matches any char after J

-- Right: escape dot to match literal dot
SELECT * FROM users WHERE name REGEXP 'J\\.';

Quick Reference

Operator	Description
REGEXP	Matches string against a regular expression pattern
NOT REGEXP	Matches string that does NOT match the pattern
^	Start of string anchor
$	End of string anchor
.	Any single character
*	Zero or more of the previous character
[abc]	Any one character inside the brackets
[a-z]	Any one character in the range a to z

Key Takeaways

Use REGEXP in MySQL to match strings with regular expressions in WHERE clauses.

Patterns are case-insensitive by default in MySQL REGEXP.

Escape special characters like dot (.) when you want to match them literally.

Use anchors like ^ and $ to match the start or end of strings.

Avoid confusing LIKE with REGEXP; use REGEXP for complex patterns.