Pattern Matching in MySQL

1. Introduction

This tutorial will study pattern-matching constructs in a MySQL (version 8) database.

We’ll use our University schema to define the queries.

2. Pattern Matching

While working on relational databases, we sometimes have to select certain rows where a particular column contains a specific word or phrase.

At first, we might try equality checks, which implies searching for complete matches.

However, in many cases, we seek partial matches. For example, we might know only the first few characters of an employee’s name, so we can’t perform an exact search.

In both cases, MySQL offers several methods for pattern matching:

3. Using LIKE

Let’s say we want to list all students whose names start with the letter R. LIKE does simple matching with two special symbols:

• % matches any number of characters, even empty strings
• _ matches a single character

So, our query will be:

SELECT * FROM Student 
WHERE name LIKE 'R%';

It returns the correct results:

+------+-----------+-------------+------------+-----------------+-----------------+------+
| id   | name      | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------+-------------+------------+-----------------+-----------------+------+
| 1003 | Rita Ora  |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1103 | Rose Rit  |  1323612067 | 2001-05-14 | 2020-01-15      | NULL            | 3.57 |
| 1610 | Ritu Raj  |  3203455662 | 2002-02-05 | 2021-01-15      | 2025-06-15      | NULL |
| 1710 | Roni Roto |  2678910631 | 2002-03-11 | 2021-01-15      | 2025-06-15      | 4.44 |
| 2001 | Reena Roy |  9023455613 | 2003-01-13 | 2022-01-15      | 2025-06-15      | 4.15 |
+------+-----------+-------------+------------+-----------------+-----------------+------+

LIKE operator is case-insensitive by default, so LIKE ‘%R’ and  LIKE ‘%r’ are equivalent. However, to perform a case-sensitive search, we use the keyword BINARY in MySQL:

SELECT * FROM Student 
WHERE name LIKE BINARY 'r%';

As expected, we get an empty set:

Empty set (0.00 sec)

We can do more complex searches. For instance, if we want to find all students whose name column (first name, last name, and space delimiting them) has exactly nine characters, we’ll use nine underscore characters in the query:

SELECT * FROM Student 
WHERE name LIKE '_________';

We get the following result:

+------+-----------+-------------+------------+-----------------+-----------------+------+
| id   | name      | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------+-------------+------------+-----------------+-----------------+------+
| 1607 | Peter Liu |  3043455662 | 2002-04-14 | 2021-01-15      | 2025-06-15      | NULL |
| 1710 | Roni Roto |  2678910631 | 2002-03-11 | 2021-01-15      | 2025-06-15      | 4.44 |
| 2001 | Reena Roy |  9023455613 | 2003-01-13 | 2022-01-15      | 2025-06-15      | 4.15 |
+------+-----------+-------------+------------+-----------------+-----------------+------+

4. Using REGEXP_LIKE()

Next, we have the REGEXP_LIKE() function for the job. Unlike the LIKE operator, which doesn’t support many features of regular expressions, REGEXP_LIKE() does.

Regular expressions (regexes) provide a straightforward framework for searching and manipulating strings. Regexes are supported in all programming languages (such as C++, Python, Java, and JavaScript) and most databases (such as MySQL, Oracle, and Cassandra).

The REGEXP_LIKE(<parent_string>,  <pattern>)function takes two arguments:

• parent_string is the string over which the match is to be performed.
• pattern is a regex to match in parent_string.

So, to find all students whose names start with the letter R, we use the following query:

SELECT * FROM Student
WHERE REGEXP_LIKE(name, '^R');

As usual, ^ denotes the start of the string. Here is the result:

+------+-----------+-------------+------------+-----------------+-----------------+------+
| id   | name      | national_id | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------+-------------+------------+-----------------+-----------------+------+
| 1003 | Rita Ora  |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1103 | Rose Rit  |  1323612067 | 2001-05-14 | 2020-01-15      | NULL            | 3.57 |
| 1610 | Ritu Raj  |  3203455662 | 2002-02-05 | 2021-01-15      | 2025-06-15      | NULL |
| 1710 | Roni Roto |  2678910631 | 2002-03-11 | 2021-01-15      | 2025-06-15      | 4.44 |
| 2001 | Reena Roy |  9023455613 | 2003-01-13 | 2022-01-15      | 2025-06-15      | 4.15 |
+------+-----------+-------------+------------+-----------------+-----------------+------+

By default, REGEXP_LIKE is case-insensitive. However, we can add a flag ‘c’ to it to make it perform case-sensitive search:

SELECT * FROM Student 
WHERE REGEXP_LIKE(name, '^r', 'c');

We get an empty set as no student’s name starts with a lowercase r:

Empty set (0.00 sec)

In contrast, REGEXP_LIKE(name, ‘^r’) without the ‘c’ flag looks for case-insensitive matches.

4.1. REGEXP_LIKE() vs LIKE

REGEXP_LIKE() succeeds if it finds a match anywhere in the string. In contrast, the entire string has to match the regex of LIKE. To demonstrate it, let’s find all courses from the computer science department whose names contain the substring ‘to’ (using JOIN):

SELECT Department.name, Course.name 
FROM Department LEFT JOIN Course ON Department.id = Course.department_id 
WHERE REGEXP_LIKE(Course.name, 'to') 
AND Department.code="CS";

We get the correct result:

+------------------+---------------------------------------------+
| name             | name                                        |
+------------------+---------------------------------------------+
| Computer Science | Introduction to Operating Systems           |
| Computer Science | Introduction to Real Time Operating Systems |
| Computer Science | Introduction to Computer Architecture       |
| Computer Science | Introduction to Databases                   |
| Computer Science | Introduction to Structured Query Language   |
| Computer Science | Introduction to Data Structures             |
| Computer Science | Introduction to Algorithms                  |
+------------------+---------------------------------------------+

Operators RLIKE and REGEXP are synonyms with REGEXP_LIKE(), so we can use them for brevity. Their syntax is:

<parent_string> RLIKE <pattern>
<parent_string> REGEXP <pattern>

5. Using LOCATE

MySQL provides the LOCATE function for a particular case of pattern matching. Its syntax is:

LOCATE(<string_to_search>, <parent_string>, [position])

Here:

• string_to_search is the substring to search for in parent_string
• parent_string is the string on which we’ll perform the search
• position is an optional argument specifying the starting position for the search (default is 1)

The LOCATE function returns the position of the first occurrence of a string_to_search in parent_string. If string_to_search isn’t in the original string, LOCATE returns 0. This function performs a case-insensitive search.

This method searches for the substring in the given string starting from the given position (default is 1, and the string follows 1-based indexing). So, to find all students whose names begin with R, we can use the following query:

SELECT * FROM Student 
WHERE LOCATE('R', name, 1)=1;

We get the following result:

+------+-----------+-------------+------------+-----------------+-----------------+------+------------------+
| id   | name      | national_id | birth_date | enrollment_date | graduation_date | gpa  | LOCATE('r',name) |
+------+-----------+-------------+------------+-----------------+-----------------+------+------------------+
| 1003 | Rita Ora  |   132345166 | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |                1 |
| 1103 | Rose Rit  |  1323612067 | 2001-05-14 | 2020-01-15      | NULL            | 3.57 |                1 |
| 1610 | Ritu Raj  |  3203455662 | 2002-02-05 | 2021-01-15      | 2025-06-15      | NULL |                1 |
| 1710 | Roni Roto |  2678910631 | 2002-03-11 | 2021-01-15      | 2025-06-15      | 4.44 |                1 |
| 2001 | Reena Roy |  9023455613 | 2003-01-13 | 2022-01-15      | 2025-06-15      | 4.15 |                1 |
+------+-----------+-------------+------------+-----------------+-----------------+------+------------------+

To find a department that doesn’t contain any vowels, we use this query:

SELECT * FROM Department 
WHERE LOCATE('a', name) < 0 
AND LOCATE('a', name) < 0 
AND LOCATE('e', name) < 0 
AND LOCATE('i', name) < 0 
AND LOCATE('o', name) < 0 
AND LOCATE('u', name) < 0;

The result is an empty set:

Empty set (0.00 sec)

6. Comparison of LIKE, LOCATE, and REGEXP_LIKE

Let’s summarize the differences between these three methods:

LIKE is the most usable of the three because most queries have a single condition for matching, and LIKE is the fastest for such cases. It is highly usable for single-condition queries, whereas REGEXP_LIKE is the preferred choice for multiple-condition queries.

Further, we can use LOCATE to get the position of a pattern string in a column or to set the position from which to look for matches.

7. Conclusion

In this article, we learned about the pattern-matching capabilities of MySQL.

We can use three constructs for this. The LIKE operator is the simplest and fastest. The LOCATE method is slower but gives us additional options to search from a specific position in the parent string. REGEXP_LIKE uses regular expressions. However, it has a slower response time.