MySQL Regular Expressions - Part 2

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'.

Code: SQL

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.

Code: SQL

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

If we want to select all names ending with 'P', then the SQL query goes like this

Code: SQL

SELECT name FROM employees WHERE name REGEXP 'P$'

We can use much complex patterns in our SQL queries, but first let's have a look at various MySQL Regular Expression metacharacters.



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

Extras



MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).







Whether the Regular Expression match is case sensitive or otherwise is decided by the collation method of the table. If your collation method name ends with ci then the comparison/match is case-insensitive, else if it end in cs then the match is case sensitive.



Examples



Checking only for numbers

Code: SQL

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

/* starts, ends and contains numbers */

Contains a specific word, for example the skill PHP in skill sets

Code: SQL

SELECT name FROM employees WHERE skill_sets REGEXP '[[:<:]]php[[:>:]]'

Fetching records where employees have entered their 10-digit mobile number as the contact number.

Code: SQL

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

For more information refer http://dev.mysql.com/doc/refman/5.1/en/regexp.html