In the previous chapter, we have learned that MySQL can be accessed through the LIKE …% To make a fuzzy match.
MySQL also supports other regular expression matching, using the REGEXP operator in MySQL for regular expression matching.
If you know PHP or Perl, the operation is very simple, because the regular expression matching of MySQL is similar to that of these scripts.
The regular patterns in the following table 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 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 ‘like’ [.n] The mode of’. |
| A collection of characters. Matches any of the characters contained. For example,’ [abc] ‘can match the’a’in ‘plain’. |
| A collection of negative characters. Matches any characters that are not included. For example,’ [^abc] ‘can match the’p’in ‘plain’. |
| Match p1 or p2 or p3. For example,’z | food’ can match’z’or ‘food’. (Z | f) ood’ matches “zood” or “food”. |
| 匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。 |
| Matches the previous subexpression one or more times. For example, ‘zo+’ can match “zo” and “zoo”, but not “z”. + is equivalent to {1,}. |
| N is a non-negative integer. Match the determined n times. For example,’o {2} ‘does not match the’ o’in ‘Bob’, but does match the two o in ‘food’. |
| M and n are non-negative integers, where n < = m. There are at least n matches and a maximum of m matches. |
Example ¶
Once we understand 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 the data in the name field that begins with ‘st’:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
Find all the data in the name field that ends with ‘ok’:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
Find all the data in the name field that contains the ‘mar’ string:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
Find all data in the name field that begins with a vowel character or ends with a ‘ok’ string:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';