May 15, 2021 MySQL
In the previous sections we learned that MySQL can be fuzzy by LIKE ...%.
MySQL also supports matching of other regular expressions, and MySQL uses reGEXP operators for regular expression matching.
If you know PHP or Perl, it's easy to do because MySQL's regular expressions match similar to those of these scripts.
The positive pattern in the following table can be applied to the REGEXP operator.
| Mode | Describe |
|---|---|
| ^ | Matches the start of the input string. If you set the Multiline property of the RegExp object, the position after ''n' or ''r' is also matched. |
| $ | Matches the end of the input string. If you set the Multiline property of the RegExp object, also matches the position before ''n' or ''r'. |
| . | Matches any single character other than "" To match any character, including ''n', use a pattern like ' |
| [...] | A collection of characters. M atches any one of the characters contained. For example, ''abc'' can match 'a' in 'plain'. |
| [^...] | A collection of negative characters. M atches any character that is not included. For example, ''abc'' can match 'p' in 'plain'. |
| p1|p2|p3 | Match p1 or p2 or p3. F or example, 'z|food' can match 'z' or 'food'. (z|f) ood' matches "zood" or "food". |
| Matches the previous subexpression zero or more times. F or example, zo can match "z" and "zoo". :: Equivalent to . . . | |
| + | Matches the previous subexpression once or more. F or example, 'zo' can match "zo" and "zoo", but not "z". The equivalent is at . |
| {n} | n is a non-negative integer. M atch the determined n times. For example, 'o{2}' does not match 'o' in "Bob", but it does match the two os in "food". |
| {n,m} | both m and n are non-negative integers, where n slt; A minimum of n matches and a maximum of m matches. |
Knowing the regular requirements above, we can write SQL statements with regular expressions based on our own needs. H ere are a few small examples (table name: person_tbl) to deepen our understanding:
Find all the data in the name field that starts 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 the data in the name field that begins with a unonic character or ends with an 'ok' string:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';