MySQL: REGEXP operator

REGEXP or regular expression is a powerful search utility for MySQL which can track down the table data within a period. Using some common patterns which are used with the REGEXP. It provides more flexibility while pattern matching by supporting metacharacters.

Let us see some of the patterns and their details used in the REGEXP operator.

• ^ (Caret)  -- Used to match a pattern with the beginning.
• $ (Dollar) -- Used to match a pattern with the ending.
• | (Logical OR) -- Used to search patterns inset with different options using the pipeline.
• [a-z] or [1-9] or [abc]  -- Matched letters or numbers within the square brackets with range or set range.

Syntax

SELECT * from TableName WHERE ColumnName REGEXP pattern;

Example

For demo purposes, we will be using the customers table and performing various operations such as:

Get customers whose

1. First names are ELKA or AMBUR
2. Last names end with EY or ON
3. Last names starting with MY or containing SE
4. Last names contain B followed by R or U

Here is the customers table.

1. First names are ELKA or AMBUR

SELECT * from customers where first_name REGEXP 'elka|ambur'

• So here using the pipeline | we have queried the first names which contain Elka and Ambur which will bring out both the search pattern when matched when this logical OR expression is used.
• In short, we can match multiple patterns.

2. Last names end with EY or ON

SELECT * from customers where last_name REGEXP 'ey$|on$'

Using $ we have fetched the last name ending with letters ey and on using this we can query out the pattern matching the ending of a string.

3. Last names starting with MY or contain SE

SELECT * from customers where last_name REGEXP '^my|se'

With the caret ^, we have queried the names which letters start with 'my' and contain 'se'.

4. Last names contain B followed by R or U

SELECT * from customers where last_name REGEXP 'b[ru]'

As we can see we should get letters 'b' followed by ru simply meaning br and bu as b is common here the search pattern of REGEXP searched this way and fetch the information.