MySQL regular expression
MySQL regular expression
In the previous chapter we have learned that MySQL can pass LIKE ...% to perform fuzzy matching.
MySQL also supports other regular expression matching. MySQL uses the REGEXP operator for regular expression matching.
If you know PHP or Perl, it's very simple, because MySQL's regular expression matching is similar to those of these scripts.
The regular patterns in the following table can be applied to the REGEXP operator.
| Pattern | Description |
|---|---|
| ^ | Matches the beginning of the input string. If the RegExp object's Multiline property is set, ^ also matches the position after '\n' or '\r'. |
| $ | Matches the end 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 collection. Matches any one of the characters contained. For example, '[abc]' matches 'a' in "plain". |
| [^...] | Negative character set. Matches any character not included. For example, '[^abc]' matches the 'p' in "plain". |
| p1|p2|p3 | Match p1 or p2 or p3. For example, 'z|food' matches "z" or "food". '(z|f)ood' matches "zood" or "food". |
| * | Matches the preceding subexpression zero or more times. For example, zo* matches "z" and "zoo". * Equivalent to {0,}. |
| + | Matches the preceding subexpression one or more times. For example, 'zo+' matches "zo" and "zoo", but not "z". + is equivalent to {1,}. |
| {n} | n is a non-negative integer. Match a certain number of n times. For example, 'o{2}' does not match the 'o' in "Bob", but it does match both o's in "food". |
| {n,m} | m and n are both non-negative integers, where n <= m. Match at least n times and at most m times. |
Example
After understanding the above regular requirements, we can write SQL statements with regular expressions according to our own needs. Below we will list a few small examples (table name: person_tbl) to deepen our understanding:
Find all data starting with 'st' in the name field:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';finds all data that ends with 'ok' in the name field:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';finds all data that contains the string 'mar' in the name field:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';Finds all data in the name field that starts with a vowel character and ends with the 'ok' string:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
Recommended related video tutorials:
