Regular expressions in SQL (usually expressed as REGEXP or RLIKE) are powerful pattern matching tools that allow you to perform complex string searches and matching operations.
Basic syntax
Most SQL implementations support the following basic syntax
SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';
Or use RLIKE
SELECT column_name FROM table_name WHERE column_name RLIKE 'pattern';
Commonly used regular expression metacharacters
| Metacharacter | describe | Example | ||
|---|---|---|---|---|
. |
Match any single character |
''Match "abc", "a c" |
||
^ |
Match the beginning of a string |
'^a'Match strings starting with a |
||
$ |
Match the end of the string |
'a$'Match a string ending with a |
||
* |
Match the previous character 0 or more times |
'a*b'Match "b", "ab", "aab" |
||
+ |
Match the previous character 1 or more times |
'a+b'Match "ab", "aab" |
||
? |
Match the previous character 0 or 1 time |
'a?b'Match "b", "ab" |
||
[] |
Match any character in brackets |
'[abc]'Match "a", "b" or "c" |
||
[^] |
Does not match any character in brackets |
'[^abc]'Match characters that are not a, b, c |
||
| ` | ` | or operator | `'a | b'` matches "a" or "b" |
() |
Grouping |
'(ab)+'Match "ab", "abab" |
||
{n} |
Match the previous character exactly n times |
'a{2}'Match "aa" |
||
{n,} |
Match the previous character at least n times |
'a{2,}'Match "aa", "aaa" |
||
{n,m} |
Match the previous character n to m times |
'a{2,4}'Match "aa", "aaa", "aaaa" |
-- Basic usage SELECT * FROM users WHERE name REGEXP '^J.*n$'; -- Case insensitive matching SELECT * FROM users WHERE name REGEXP BINARY '^[A-Z]'; -- replace SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone FROM customers;
Practical Examples
Verify email format
SELECT email FROM users WHERE email REGEXP '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$';Extract numbers from strings
-- MySQL SELECT REGEXP_SUBSTR(description, '[0-9]+') AS numbers FROM products; -- PostgreSQL SELECT (regexp_matches(description, '[0-9]+'))[1] AS numbers FROM products;
Find records containing special characters
SELECT * FROM comments WHERE content REGEXP '[^a-zA-Z0-9 ]';
Match dates in a specific format
SELECT * FROM logs WHERE entry_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';Performance considerations
- Regular expressions are usually more resource-consuming than simple LIKE operations
- Care should be taken when using on large tables, consider adding indexes or using other filtering conditions to narrow down the result set
- Some databases allow the creation of function-based indexes to speed up regular expression queries
Regular expressions are powerful tools in SQL, but they need to adjust the syntax and usage methods according to the specific database system.
This is the end of this article about SQL REGEXP regular expressions. For more related SQL REGEXP regular expression content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!