Using Regular Expressions in Mysql

Using Regular Expressions in queries

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 meta characters.

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

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.

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

SELECT name FROM employees WHERE name REGEXP 'P$'

Checking only for numbers

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

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

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

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

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