MySQL Regular Expression

In the previous blog post we have learned that MySQL can be fuzzy matched by a like ...% .

MySQL also supports the matching of other regular expressions, using the REGEXP operator to match regular expressions in MySQL.

If you know PHP or Perl, it's very easy to operate because MySQL's regular expression matches those scripts.

The regular mode in the following table can be applied to the REGEXP operator.

Mode	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 a pattern like ' [. \ n] '.
[...]	The character set is combined. Matches any one of the characters contained. For example, ' [ABC] ' can match ' a ' in ' plain '.
[^...]	Negative character set. Matches any character that is not contained. For example, ' [^ABC] ' can match ' P ' in ' plain '.
P1|p2|p3	Match P1 or P2 or p3. For example, ' Z|food ' can match "z" or "food". ' (z|f) Ood ' matches "Zood" or "food".
*	Matches the preceding subexpression 0 or more times. For example, zo* can match "z" and "Zoo". * Equivalent to {0,}.
+	Matches the preceding subexpression one or more times. For example, ' zo+ ' can match "Zo" and "Zoo", but not "Z". + equivalent to {1,}.
N	N is a non-negative integer. Matches the determined n times. For example, ' o{2} ' cannot match ' o ' in ' Bob ', but can match two o in ' food '.
{N,m}	Both M and n are non-negative integers, where n <= m. Matches at least n times and matches up to M times.

Instance

Knowing the regular requirements above, we can write SQL statements with regular expressions more of our own needs. Below we will list a few small instances (table name: PERSON_TBL) to deepen our understanding:

Look for all the data in the Name field that begins with ' St ':

MySQL>SELECTfromWHERE'^st';

Look for all the data that ends with ' OK ' in the Name field:

MySQL>SELECTfromWHERE'ok$';

Find all data that contains the ' Mar ' string in the Name field:

MySQL>SELECTfromWHERE'mar';

Find all data in the Name field that begins with a vowel character and ends with an ' OK ' string:

MySQL>SELECTfromWHERE'^[aeiou]|ok$';

MySQL Regular Expression