MySQL REGEXP Keyword

The REGEXP keyword in MySQL is used for pattern matching using regular expressions. It allows you to search for complex patterns in strings within your database tables.

Usage

The REGEXP keyword is typically used in the WHERE clause to filter records based on whether a string matches a specified regular expression pattern. It is a powerful tool for text searching and pattern recognition. MySQL's regular expressions are based on the POSIX standard and may differ slightly from other regex implementations.

SELECT column_name
FROM table_name
WHERE column_name REGEXP 'pattern';

In this syntax, REGEXP 'pattern' checks if column_name matches the given regular expression pattern. Note that REGEXP is case-insensitive by default. To perform case-sensitive matching, use the BINARY keyword:

SELECT column_name
FROM table_name
WHERE BINARY column_name REGEXP 'pattern';

Examples

1. Basic Pattern Matching

SELECT * 
FROM products 
WHERE product_name REGEXP '^C';

This example selects all rows from the products table where product_name starts with the letter 'C'. The '^C' pattern uses ^ to indicate the start of the string, ensuring that only names beginning with 'C' are matched.

2. Matching Multiple Patterns

SELECT email 
FROM users 
WHERE email REGEXP 'gmail|yahoo';

Here, the query retrieves emails from the users table that contain either 'gmail' or 'yahoo'.

3. Complex Pattern Matching

SELECT phone_number 
FROM contacts 
WHERE phone_number REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

This example selects phone numbers from contacts matching the pattern for North American phone numbers in the format XXX-XXX-XXXX.

Tips and Best Practices

• Optimize performance. Regular expressions can be resource-intensive; use them judiciously and consider indexing columns where possible. Use EXPLAIN to analyze query performance when using regular expressions.
• Test patterns separately. Test your regular expressions in a separate environment to ensure they perform as expected before applying them to your queries.
• Use anchors wisely. Use ^ and $ to denote the start and end of a string, respectively, for more precise pattern matching.
• Escape special characters. If your pattern includes special characters, ensure they are properly escaped to avoid unexpected matches. For example, to match a literal dot, use \.:

WHERE column_name REGEXP '\\.'

• Compatibility note. While MySQL supports basic regular expressions, it does not support features like lookaheads and lookbehinds.
• Difference from LIKE. Unlike REGEXP, which supports complex patterns, LIKE is used for simpler pattern matching with wildcards % and _. Use LIKE for straightforward patterns and REGEXP for more complex requirements.