MySQL Regular Expressions
In previous chapters, we have learned that MySQL can perform fuzzy matching using LIKE ...%.
MySQL also supports other regular expression matching, using the REGEXP operator for regular expression matching.
If you are familiar with PHP or Perl, it will be very simple to operate, as MySQL's regular expression matching is similar to these scripts.
The regular patterns in the table below can be applied to the REGEXP operator.
| Pattern | Description |
|---|---|
| ^ | Matches the starting position of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after '\n' or '\r'. |
| $ | Matches the ending position of the input string. If the Multiline property of the RegExp object is set, $ also matches the position before '\n' or '\r'. |
| . | Matches any single character except "\n". To match any character including '\n', use a pattern like '[.\n]'. |
| [...] | Character set. Matches any one of the included characters. For example, '[abc]' can match 'a' in "plain". |
| [^...] | Negated character set. Matches any character that is not included. For example, '[^abc]' can match 'p' in "plain". |
| p1|p2|p3 | Matches p1 or p2 or p3. For example, 'z|food' can match "z" or "food". '(z|f)ood' matches "zood" or "food". |
| * | Matches the preceding subexpression zero or more times. For example, 'zo*' can match "z" and "zoo". * is equivalent to {0,}. |
| + | Matches the preceding subexpression one or more times. For example, 'zo+' can match "zo" and "zoo", but not "z". + is equivalent to {1,}. |
| {n} | n is a non-negative integer. Matches exactly n times. For example, 'o{2}' cannot match 'o' in "Bob", but can match the two o's in "food". |
| {n,m} | m and n are non-negative integers, where n <= m. Matches at least n times and at most m times. |
Examples
After understanding the above regular expression requirements, we can write SQL statements with regular expressions according to our needs. Below, we will list a few small examples (table name: person_tbl) to deepen our understanding:
Find all data in the name field that starts with 'st':
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
Find all data in the name field that ends with 'ok':
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
Find all data in the name field that contains the string 'mar':
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
Find all data in the name field that starts with a vowel character or ends with the string 'ok':
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';