MySQL REGEXP – Search using regular expressions in MySQL Tables

Mysql Regexp

In this tutorial, we will see what is a MySQL REGEXP operator and how to use it for advanced search using a regular expression.

What is a Regular Expression?

A regular expression is a great way to perform complex searches. Unlike the LIKE operator, where you can use only two signs- %(percent) and _(underscore) to create a search pattern, the regular expression offers a bunch of operators to make a search pattern.

Using regular expressions, we can create any type of pattern to match the records which cannot be done using the LIKE operator in MySQL.

Regular expressions are available in every programing language as well as a database.

Some of the real-time applications of the regular expression are to search email ids, phone numbers, IP addresses etc. Moreover, if you want to search email ids that only belong to your organization, you can do that using a regular expression.

Introduction to MySQL REGEXP

Using a REGEXP operator, we can use a regular expression and match that pattern with the column values.

The syntax of a MySQL REGEXP is as follows-

SELECT col_names FROM table_name WHERE column_name REGEXP pattern;
Code language: SQL (Structured Query Language) (sql)

Here, every value in the column_name column will match with the regular expression pattern.

If a match happens, the expression returns true, otherwise, it returns false.

Apart from the REGEXP, you can use the RLIKE operator which is a synonym for the REGEXP.

Regular Expression Characters

CharacterBehavior
^matches the position at the beginning of the searched string
$matches the position at the end of the searched string
.matches any single character
[…]matches any character specified inside the square brackets
[^…]matches any character not specified inside the square brackets
p1|p2matches any of the patterns p1 or p2
*matches the preceding character zero or more times
+matches preceding character one or more times
{n}matches n number of instances of the preceding character
{m,n}matches from m to n number of instances of the preceding character

MySQL REGEX Examples

Before proceeding to the examples, we have to create a table and insert data into it.

Here, we have already created a table. Take a look at the table description and the data inserted into it.

Emps Table Description
Emps Table Description
Emps Table Data
Emps Table Data

Let’s take a simple example first.

Example 1 – Regex Find Data Starting With a Specific Letter

Here, we will find the employees whose names start with the letter ‘r’.

SELECT * FROM emps WHERE name REGEXP '^r';
Code language: SQL (Structured Query Language) (sql)

Here, we will check if the employee’s name starts with the letter r. However, the regular expression will ignore the case sensitivity and we will get results that contain both – ‘r’ and ‘R’.

Regular Expression Example 1
Regular Expression Example 1

Example 2 – Returning Data Case-Sensitively

If you want to create a pattern that returns results based on a case-sensitive fashion, you can use the following pattern.

SELECT * FROM emps WHERE name REGEXP '(?-i)^R'; SELECT * FROM emps WHERE name REGEXP '(?-i)^r';
Code language: SQL (Structured Query Language) (sql)

Here, the first query will return the expected result but the second query will not as there is no name that starts with the small letter ‘r’.

Regular Expression Example 2
Regular Expression Example 2

Here you can see the output of both queries.

Example 3 – Finding Data That Ends With A Specific Letter

If you want to return the names that end with the letter ‘e’, you can use the following pattern.

SELECT * FROM emps WHERE name REGEXP 'e$';
Code language: SQL (Structured Query Language) (sql)
Regular Expression Example 3
Regular Expression Example 3

Example 4 – Find Data With Specific Character Count

To find the names which have a character count of 5, you can use the following pattern.

SELECT * FROM emps WHERE name REGEXP '^.{5}$';
Code language: SQL (Structured Query Language) (sql)

Here, the letters ^ and $ match the beginning and the ending of the name.

Whereas, the character “.” indicates to match any character in the name for {5} times.

Regular Expression Example 4
Regular Expression Example 4

As you can see here, we have got the expected output.

Conclusion

In this tutorial, we have learned what is a regular expression and the special characters used in the regular expression. We also learned what is a REGEXP operator and how to use it to match the pattern with the table values. Remember, there is an endless possibility of creating regular expressions. You can create a regular expression for literally everything. So, it’s worth investing good time in the topic so that it will help you in real-time projects.