MySQL REGEXP – Search using regular expressions in MySQL Tables

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

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.

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

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

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)

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.

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.