How to Use MySQL Regular Expressions?
If you’ve ever needed to filter your MySQL database using patterns instead of exact matches, you’ve probably heard of REGEXP. Short for Regular Expression, this powerful feature lets you search, extract, and manipulate string data more flexibly than the classic LIKE operator.
In this blog, we’ll explore how to use MySQL regular expressions in a way that’s easy to understand—whether you’re just starting or you’re looking to level up your SQL game.
What Are MySQL Regular Expressions?
Regular expressions (regex) are a way to define search patterns. MySQL supports regex via the REGEXP and RLIKE operators. Both are the same—RLIKE is just an alias for REGEXP.
You can use regex to match text strings using flexible rules. It’s perfect when LIKE just doesn’t cut it.
REGEXP Syntax in MySQL
SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';
Example:
SELECT name FROM users WHERE name REGEXP '^A';
This query selects all names that start with the letter A.
Most Common Regex Patterns in MySQL
Here’s a quick cheat sheet of the most-used regex symbols in MySQL:
| Symbol | Meaning |
| * | Zero or more instances of string preceding it |
| + | One or more instances of strings preceding it |
| . | Any single character |
| ? | Match zero or one instance of the strings preceding it. |
| ^ | caret(^) matches Beginning of string |
| $ | End of string |
| [abc] | Any character listed between the square brackets |
| [^abc] | Any character not listed between the square brackets |
| [A-Z] | Match any uppercase letter. |
| [a-z] | Match any lowercase letter |
| [0-9] | Match any digit from 0 through to 9. |
| [[:<:]] | Matches the beginning of words. |
| [[:>:]] | Matches the end of words. |
MySQL REGEXP Examples (with Sample Dataset and Output)
Let’s use a sample employees table:
CREATE TABLE employees (id INT, name VARCHAR(100));
INSERT INTO employees (id, name) VALUES (1, 'John'), (2, 'Alice'), (3, 'Jason'), (4, 'Karen'), (5, 'Samson'), (6, 'Anjali');
It will create our sample employees table:
| ID | Name |
| 1 | John |
| 2 | Alice |
| 3 | Jason |
| 4 | Karen |
| 5 | Samson |
| 6 | Anjali |
1. Starts With Specific Letter
SELECT name FROM employees WHERE name REGEXP '^J';
Matches names starting with J.
Output:
| John |
| Jason |
2. Ends With Specific Letters
SELECT name FROM employees WHERE name REGEXP 'son$';
Finds names ending in son.
Output:
| Jason |
| Samson |
3. Contains Substring
SELECT name FROM employees WHERE name REGEXP 'li';
Find names that contain li.
Output:
| Alice |
| Anjali |
4. Contains Any of Specific Characters
SELECT name FROM employees WHERE name REGEXP '[abc]';
Returns names that include a, b, or c.
Output:
| Alice |
| Jason |
| Karen |
| Samson |
| Anjali |
5. Match Exactly N Characters
SELECT name
FROM employees
WHERE name REGEXP '^.{6}$';
Filter names with exactly 6 characters
Output:
| Samson |
| Anjali |
Advanced Regex Functions in MySQL 8.0+
Starting in MySQL 8.0, you get access to built-in regex functions:
- REGEXP_LIKE(col, pattern[, match_type]) – Returns true if pattern matches.
Example: Select names that contain a capital “A” (case-sensitive):
SELECT name FROM employees WHERE REGEXP_LIKE(name, 'A', 'c');
Result:
| Alice |
| Anjali |
Here, the character ‘c’ is the optional match_type argument specifying to perform Case-sensitive matching.
- REGEXP_INSTR(col, pattern) – Returns index of match.
Example: Find where the letter “s” first appears in each name (case-insensitive):
SELECT name,REGEXP_INSTR(name, 's', 1, 1, 0, 'i') AS position FROM employees;
Result:
| name | position |
| John | 0 |
| Alice | 0 |
| Jason | 0 |
| Karen | 4 |
| Samson | 0 |
| Anjali | 2 |
- REGEXP_SUBSTR(col, pattern) – Extracts substring that matches.
Example: Extract the names that matches ‘a’ followed by ‘l’ or ‘m’:
SELECT name FROM employees WHERE REGEXP_SUBSTR (name, 'a[lm]', 1, 1) IS NOT NULL;
Result:
| Alice |
| Samson |
| Anjali |
- REGEXP_REPLACE(col, pattern, replacement) – Replaces matched text.
Example: Replace all vowels with *:
SELECT name, REGEXP_REPLACE(name, '[aeiou]', '*', 1, 0, 'c') AS redacted_name FROM employees;
Result:
| name | redacted_name |
| John | J*hn |
| Alice | *l*c* |
| Jason | J*s*n |
| Karen | K*r*n |
| Samson | S*ms*n |
| Anjali | *nj*l* |
Performance Tips
Regex can be slow on large datasets. Here’s how to keep it snappy:
- Combine regex with indexed filters (WHERE id%2=0 AND name REGEXP ‘^A’;)
- Avoid using regex on huge, unfiltered result sets
- Use EXPLAIN to analyze query plans
Final Thoughts
MySQL regular expressions are your secret weapon when dealing with flexible text patterns. Whether you’re cleaning data, validating inputs, or just looking for creative search conditions—regex is the tool.
Start simple, practice often, and keep this guide handy!
Frequently Asked Questions (FAQs)
1. What is the difference between REGEXP and RLIKE in MySQL?
There is no difference. RLIKE is a synonym for REGEXP and both can be used interchangeably.
2. Is MySQL REGEXP case-sensitive?
By default, REGEXP is case-insensitive. If you need case-sensitive matches, use the BINARY keyword or the c match type in functions like REGEXP_LIKE().
3. Can I use REGEXP with numbers?
Yes. You can use patterns like [0-9] or [:digit:] to match numeric characters in a string.
4. What version of MySQL supports regex functions like REGEXP_SUBSTR?
These advanced regex functions are supported in MySQL 8.0 and above.
5. Is REGEXP slower than LIKE?
Generally, yes. REGEXP is more flexible but also more resource-intensive. Use it when necessary, and filter large datasets beforehand to avoid performance issues.
6. Can I combine REGEXP with other SQL clauses?
Absolutely. You can combine it with AND, OR, ORDER BY, and other clauses like in any standard SQL query.
7. What does [:<:] and [:>:] mean in regex?
These POSIX classes match word boundaries— the start and end of words, respectively.
