How to Use REGEXP_LIKE in MySQL for Pattern Matching

In MySQL 8.0 and later, use REGEXP_LIKE(column, pattern) to check if a string matches a regular expression pattern. It returns 1 if the pattern matches and 0 if it does not, allowing flexible text searches in queries.

Syntax

The REGEXP_LIKE function checks if a string matches a regular expression pattern.

• expr: The string or column to test.
• pattern: The regular expression pattern to match.
• match_type (optional): Flags to modify matching behavior, like case sensitivity.

sql

REGEXP_LIKE(expr, pattern [, match_type])

Example

This example shows how to find names starting with 'J' in a table using REGEXP_LIKE.

sql

CREATE TABLE people (id INT, name VARCHAR(50));

INSERT INTO people VALUES
(1, 'John'),
(2, 'Alice'),
(3, 'Jack'),
(4, 'Bob');

SELECT name FROM people WHERE REGEXP_LIKE(name, '^J');

Output

name
John
Jack

Common Pitfalls

Common mistakes include:

• Using REGEXP_LIKE in MySQL versions before 8.0, where it is not supported.
• Forgetting to anchor patterns with ^ or $ when needed.
• Confusing REGEXP_LIKE with the older REGEXP operator syntax.

Example of wrong and right usage:

sql

/* Wrong in MySQL 5.7 or earlier: */
SELECT name FROM people WHERE name REGEXP '^J';

/* Right in MySQL 8.0+: */
SELECT name FROM people WHERE REGEXP_LIKE(name, '^J');

Quick Reference

Parameter	Description
expr	String or column to test
pattern	Regular expression pattern
match_type	Optional flags like 'i' for case-insensitive

Key Takeaways

Use REGEXP_LIKE(expr, pattern) in MySQL 8.0+ to match strings with regex.

It returns 1 if the pattern matches, 0 otherwise.

Anchor your patterns with ^ and $ to match start or end of strings.

REGEXP_LIKE is not available in MySQL versions before 8.0.

Use optional match_type flags like 'i' for case-insensitive matching.