sql – How to regex in a MySQL query

Regular expressions (regex) can be used in MySQL queries through the REGEXP or REGEXP_LIKE operator. They allow you to match patterns in strings and are particularly useful for filtering or validating data based on specific formats.

1. Using REGEXP

The REGEXP operator (or RLIKE, which is a synonym) allows you to filter rows where a column’s value matches a given regex pattern.

Example: Basic Pattern Matching

SELECT * 
FROM employees
WHERE name REGEXP '^A'; -- Names starting with 'A'

Explanation:

• ^A: Matches strings that start with “A”.

Example: Complex Patterns

SELECT * 
FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

Explanation:

• Matches valid email addresses.

2. Using REGEXP_LIKE (MySQL 8.0+)

Starting from MySQL 8.0, the REGEXP_LIKE function can be used for regex matching with more flexibility.

Syntax:

SELECT *
FROM table_name
WHERE REGEXP_LIKE(column_name, 'pattern', 'match_type');

• pattern: The regex pattern to match.
• match_type: Optional. Used to specify case sensitivity ('c' for case-sensitive, 'i' for case-insensitive).

Example: Case-Insensitive Matching

SELECT * 
FROM products
WHERE REGEXP_LIKE(name, '^phone', 'i'); -- Names starting with 'phone', case-insensitive

3. Match Types

In REGEXP_LIKE, you can use the following match types:

• 'c': Case-sensitive (default).
• 'i': Case-insensitive.

Regex Syntax in MySQL

MySQL uses a subset of POSIX Extended Regular Expressions.

Common Patterns:

1. ^ and $: Anchors for the beginning and end of a string.

   SELECT * FROM users WHERE name REGEXP '^John$'; -- Matches "John" exactly
   

2. .: Matches any single character.

   SELECT * FROM users WHERE phone REGEXP '123.567'; -- Matches "1234567" or "123a567"
   

3. [...]: Matches any character in the brackets.

   SELECT * FROM users WHERE grade REGEXP '[A-C]'; -- Matches "A", "B", or "C"
   

4. *, +, ?: Quantifiers for repetition.
   • *: Zero or more occurrences.
   • +: One or more occurrences.
   • ?: Zero or one occurrence.

   SELECT * FROM logs WHERE message REGEXP 'error*'; -- Matches "err", "error", "errorr", etc.
   

5. {n,m}: Matches between n and m occurrences.

   SELECT * FROM users WHERE phone REGEXP '[0-9]{10}'; -- Matches 10-digit phone numbers
   

4. Example Use Cases

Match Phone Numbers

SELECT * 
FROM customers
WHERE phone REGEXP '^[0-9]{10}$'; -- Matches exactly 10 digits

Filter Emails

SELECT * 
FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

Case-Insensitive Search

SELECT * 
FROM employees
WHERE name REGEXP 'john|JOHN'; -- Matches "john" or "JOHN"

5. Limitations of MySQL Regex

1. MySQL regex support is limited to a subset of POSIX Extended Regular Expressions.
2. More advanced regex features (e.g., lookaheads/lookbehinds) are not supported.
3. Regex operations can be slow on large datasets; consider indexing or optimizing the query if performance is an issue.

By combining REGEXP or REGEXP_LIKE with these patterns, you can handle various text-search and validation scenarios in MySQL queries.