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 '\\.'
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.