Article directory
MySQL databaseHow does the regular expression match?MySQL regexp like usage
MySQL regular expressions
In the previous chapters we have learned that MySQL can be LIKE...% for fuzzy matching.
MySQL also supports the matching of other regular expressions. The REGEXP operator is used in MySQL for regular expression matching.
If you know PHP or Perl, it's pretty straightforward, as 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.
| 模式 | Description |
|---|---|
| ^ | Matches the beginning of the input string.^ also matches the position after '\n' or '\r' if the Multiline property of the RegExp object is set. |
| $ | Matches the end 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]'. |
| [...] | collection of characters.Matches any one of the contained characters.For example, '[abc]' would match "plai'a' in n". |
| [^…] | Negative character set.Matches any character not contained.For example, '[^abc]' would match 'p' in "plain". |
| p1|p2|p3 | Matches p1 or p2 or p3.For example, 'z|food' would match either "z" or "food". '(z|f)ood' matches "zood" or "food". |
| * | Matches the preceding subexpression zero or more times.For example, zo* would match "z" as well as "zoo". * is equivalent to {0,}. |
| + | Matches the preceding subexpression one or more times.For example, 'zo+' would match "zo" and "zoo", but not "z". + is equivalent to {1,}. |
| {not} | n is a non-negative integer.Matches exactly n times.For example, 'o{2}' would not match the 'o' in "Bob", but would match both o's in "food". |
| {n,m} | Both 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 requirements, we can write SQL statements with regular expressions according to our own requirements.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';
Find all data that ends with 'ok' in the name field:
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
Find all data containing the 'mar' string in the name field:
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$';
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "How to match MySQL database regular expressions? MySQL regexp like usage" will help you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-492.html