MySQL Regular Expressions

MySQL Regular Expressions is used for specifying a pattern for a complex search.To invoke Regular Expressions in MySQL we use REGEXP keyword.MySQL Regular Expressions are very similar to PHP and PERL scripting languages Regular Expressions.

Syntax

SELECT column_name1,column_name2 FROM table_name WHERE column_name REGEXP 'regexp'

MySQL allows the following Regular Expressions metacharacters.

Pattern Description
^ Beginning of string
$ End of string
. Match Any single character
[^...] Match characters not listed between the square brackets
P1|P2|P3 Match any of the patterns P1, P2, or P3
* Match Zero or more instances of preceding element.
+ Match One or more instances of preceding element
{n} Match n instances of preceding element
{m,n} Match m through n instances of preceding element
[[:< :]] Match beginning of words.
[[:>:]] Match ending of words.
[[:alnum:]] Match any number or letter. Equivalent to [a-z], [A-Z] and [0-9].
[[:alpha:]] Match any letter. Equivalent to [a-z] and [A-Z].
[[:blank:]] Match Space or Tab.Equivalent to [\\t] and [ ]
[[:digit:]] Equivalent to [0-9]
[[:lower:]] Match Lower case letters. Equivalent to [a-z]
[[:punct:]] Match Characters that are neither control characters, nor alphanumeric (i.e punctuation characters)
[[:space:]] Match Any whitespace character (new line,tab,and space etc)
[[:upper:]] Match Upper case letters. Equivalent to [A-Z].
[[:xdigit:]] Match Any hexadecimal digit. Equivalent to [A-F], [a-f] and [0-9].

Examples of MySQL REGEXP

Use of ^

MySQL Query to find all the names starting with 'mi'

SELECT name FROM employee WHERE name REGEXP '^mi';

Use of $

MySQL Query to find all the names ending with 'ra'

SELECT name FROM employee WHERE name REGEXP 'ra$';

[...]

MySQL Query to find all the names containing 'z' or 'v' or 'y'

SELECT name FROM employee WHERE name REGEXP "[zvy]";

[:digit:]]

MySQL Query to find all the product_name containing MII5O .

SELECT * FROM product WHERE product_name REGEXP 'MII[[:digit:]]';