MySQL Reference Manual

Appendix F. MySQL Regular Expressions

A regular expression (regex) is a powerful way of specifying a complex search.

MySQL uses Henry Spencer’s implementation of regular expressions, which is aimed at conformance with POSIX 1003.2. MySQL uses the extended version.

This is a simplistic reference that skips the details. To get more exact information, see Henry Spencer’s regex(7) manual page, which is included in the source distribution. See Appendix C.

A regular expression describes a set of strings. The simplest regexp is one that has no special characters in it. For example, the regexp hello matches hello and nothing else.

Non-trivial regular expressions use certain special constructs so that they can match more than one string. For example, the regexp hello|word matches either the string hello or the string word.

As a more complex example, the regexp B[an]*s matches any of the strings Bananas, Baaaaas, Bs, and any other string starting with a B, ending with an s, and containing any number of a or n characters in between.

A regular expression may use any of the following special characters/constructs:

^     

Match the beginning of a string.

mysql> SELECT "fo\nfo" REGEXP "^fo$";           -> 0
mysql> SELECT "fofo" REGEXP "^fo";              -> 1

$     

Match the end of a string.

mysql> SELECT "fo\no" REGEXP "^fo\no$";         -> 1
mysql> SELECT "fo\no" REGEXP "^fo$";            -> 0

.       

Match any character (including newline).

mysql> SELECT "fofo" REGEXP "^f.*";             -> 1
mysql> SELECT "fo\nfo" REGEXP "^f.*";           -> 1

a*     

Match any ...