Latest web development tutorials

MySQL Regular Expressions

In the previous section we have learned through the MySQL LIKE ...% for fuzzy matching.

MySQL also supports other regular expression matching, MySQL REGEXP operator to use regular expression matching.

If you know PHP or Perl, then the operation is very simple, because MySQL regular expression matching similar to these scripts.

In the table below normal mode it can be used in the REGEXP operator.

mode description
^ Matches the beginning of the string. If you set the Multiline property of the RegExp object, ^ also matches '\ n' position or '\ r' after.
$ Match the input end of the string. If you set the Multiline property RegExp object, also matching $ '\ n' or '\ r' position before.
. Matches any single character except "\ n" is. To match including '\ n', including any characters, like the use of '[. \ N]' mode.
[...] Set of characters. Matches any character included. For example, '[abc]' matches "plain" in the 'a'.
[^ ...] Negative character set. Matches any character that is not included. For example, '[^ abc]' matches "plain" in the 'p'.
p1 | p2 | p3 Match p1, p2, or p3. For example, 'z | food' can match the "z" or "food". '(Z | f) ood' will match "zood" or "food".
* Matches the preceding subexpression zero or more times. For example, zo * can match the "z" and "zoo". * Equivalent to {0,}.
+ Matches the preceding subexpression one or more times. For example, 'zo +' will match "zo" and "zoo", but can not match the "z". + Is equivalent to {1}.
{N} n is a non-negative integer. Matching the determined n times. For example, 'o {2}' does not match the "Bob" in the 'o', but can match the "food" in the two o.
{N, m} m and n are non-negative integers, where n <= m. Match at least n times and match up to m times.

Examples

Once you know more regular needs, we can be more to their own needs to write SQL statement with a regular expression. Below we will list a few small examples (table name: person_tbl) to deepen our understanding:

Find all of the data field name to 'st' beginning with:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

Find all of the data field name to 'ok' for the ending:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

Find a name field contains all the data 'mar' strings:

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

Find a name field to begin with vowels and with 'ok' end of the string of all data:

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';