English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
In the previous chapters, we have learned that MySQL can use LIKE ...% to perform fuzzy matching.
MySQL also supports other regular expression matching, MySQL uses the REGEXP operator to perform regular expression matching.
If you are familiar with PHP or Perl, it is very simple to operate because MySQL's regular expression matching is similar to these scripts.
The regular expression patterns in the table below can be applied to the REGEXP operator.
| Pattern | Description |
|---|---|
| ^ | Matches the start 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 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 character included. For example, '[abc]' can match 'a' in 'plain'. |
| ^[...] | Negative character set. Matches any character not included. For example, '[^abc]' can match 'p' in 'plain'. |
| p1|p2|p3 | Matches p1 Or p2 Or p3For 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+The character 'o' can match 'zo' and 'zoo', but cannot match 'z'.+ Is equivalent to {1,}。 |
| {n} | n is a non-negative integer. Matches exactly n times. For example, 'o{2The character 'o' cannot match 'o' in 'Bob', but it can match two 'o's in 'food'. |
| {n,m} | m and n are non-negative integers, where n <= m. Match at least n times and at most m times. |
After understanding the above regular expression 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 where the 'name' field starts with the string 'st':
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
Find all data where the 'name' field ends with the string 'ok':
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
Find all data where the 'name' field contains the string 'mar':
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
Find all data where the 'name' field starts with a vowel character or ends with the string 'ok':
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';