Regular Expressions in MySQL

Introduction
A very interesting and useful capability of MySQL is to incorporate Regular Expressions (regex) in SQL queries. The regular expression support in MySQL is extensive. Let's take a look at using Regular Expressions in queries and the supported metacharacters.

Using Regular Expressions in queries
A simple example of using Regular Expressions in a SQL query would be to select all names from a table that start with 'A'.

eg:-

SELECT name FROM employees WHERE name REGEXP '^A'

A slight modification in the above example to look for names starting with 'A' or 'D' or 'F' will look like this.

eg:-

SELECT name FROM employees WHERE name REGEXP '^(A|D|F)'

Regular Expression Metacharacters
*
Matches zero or more instances of the string preceding it
+
Matches one or more instances of the string preceding it
?
Matches zero or one instances of the string preceding it
.
Matches any single character, except a newline
[xyz]
Matches any of x, y, or z (match one of enclosed characters)
[^xyz]
Matches any character not enclosed
[A-Z]
Matches any uppercase letter
[a-z]
Matches any lowercase letter
[0-9]
Matches any digit
^
Anchors the match from the beginning
$
Anchors the match to the end
|
Separates alternatives
{n,m}
String must occur at least n times, but not more than m times
{n}
String must occur exactly n times
{n,}
String must occur at least n times
[[:<:]] Matches beginning of words
[[:>:]]
Matches ending of words
[:class:]
match a character class i.e.,
[:alpha:] for letters
[:space:] for whitespace
[:punct:] for punctuation
[:upper:] for upper case letters

Examples

SELECT age FROM employees WHERE age REGEXP '^[0-9]+$'

/* starts, ends and contains numbers */

SELECT name FROM employees WHERE contact_no REGEXP '^[0-9]{10}$'