How the LIKE Operator Works in MariaDB

Posted on by Ian

In MariaDB, the LIKE operator allows you to do pattern matching. It determines whether a specific character string matches a specified pattern. It returns either 1 (TRUE) or 0 (FALSE).

A pattern can include regular characters, as well the % and _ wildcard characters.

Those wildcard characters are explained in the following table.

Wildcard CharacterDescription
%Matches any string of zero or more characters. It can be used as either a prefix or a suffix, and it can also be used in the middle of a string.
_Matches any single character.

Syntax

The syntax goes like this:

expr LIKE pat [ESCAPE 'escape_char']

Example

Suppose we have the following table:

SELECT * FROM Pets;

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     3 |         2 |       2 | Scratch | 2018-10-01 |
|     4 |         3 |       3 | Wag     | 2020-03-15 |
|     5 |         1 |       1 | Tweet   | 2020-11-28 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
|     7 |         3 |       2 | Bark    | NULL       |
|     8 |         2 |       4 | Meow    | NULL       |
+-------+-----------+---------+---------+------------+

Here’s an example to demonstrate how the LIKE operator works. 

SELECT *
FROM Pets
WHERE PetName LIKE 'F%';

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
+-------+-----------+---------+---------+------------+

In this example, I used the LIKE operator in conjunction with the % wildcard operator to return pets whose name starts with the letter F.

Case Sensitivity

LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive.

Therefore, we can change the previous example to use a lowercase f, and still get the same result:

SELECT *
FROM Pets
WHERE PetName LIKE 'f%';

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
+-------+-----------+---------+---------+------------+

However, we can force a case-sensitive match by using the COLLATE clause with a binary collation. Alternatively, you can use CAST() to coerce it to a binary string.

Example:

SELECT *
FROM Pets
WHERE PetName LIKE 'f%' COLLATE utf8_bin;

Result:

Empty set (0.000 sec)

No results are returned.

But if we change it to an uppercase F:

SELECT *
FROM Pets
WHERE PetName LIKE 'F%' COLLATE utf8_bin;

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
+-------+-----------+---------+---------+------------+

We get three matches, as expected.

Numeric Arguments

Numeric arguments are coerced to binary strings.

SELECT * 
FROM Pets 
WHERE PetId LIKE 7;

Result:

+-------+-----------+---------+---------+------+
| PetId | PetTypeId | OwnerId | PetName | DOB  |
+-------+-----------+---------+---------+------+
|     7 |         3 |       2 | Bark    | NULL |
+-------+-----------+---------+---------+------+

Dates

Here’s an example that matches the year from a date:

SELECT * 
FROM Pets 
WHERE DOB LIKE '2020%';

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     4 |         3 |       3 | Wag     | 2020-03-15 |
|     5 |         1 |       1 | Tweet   | 2020-11-28 |
|     6 |         3 |       4 | Fluffy  | 2020-09-17 |
+-------+-----------+---------+---------+------------+

You can also use the LIKE operator against the result of date functions, such as DAYNAME(), MONTHNAME(), etc.

SELECT * 
FROM Pets 
WHERE MONTHNAME(DOB) LIKE 'Nov%';

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     1 |         2 |       3 | Fluffy  | 2020-11-20 |
|     5 |         1 |       1 | Tweet   | 2020-11-28 |
+-------+-----------+---------+---------+------------+

The _ Wildcard Operator

The underscore (_) wildcard operator matches any single character.

Example:

SELECT *
FROM Pets
WHERE PetName LIKE '_e%';

Result:

+-------+-----------+---------+---------+------------+
| PetId | PetTypeId | OwnerId | PetName | DOB        |
+-------+-----------+---------+---------+------------+
|     2 |         3 |       3 | Fetch   | 2019-08-16 |
|     8 |         2 |       4 | Meow    | NULL       |
+-------+-----------+---------+---------+------------+

In this case, we matched pet names where the second character is an e.

The Escape Character

Sometimes you may find yourself in the situation where you need to search for an underscore or a percentage sign. In such cases, you can use a backslash (\) to escape these characters. This will prevent them from being interpreted as wildcard characters.

Suppose we have the following table:

SELECT * FROM Owners;

Result:

+---------+-----------+----------+----------------+--------------------+
| OwnerId | FirstName | LastName | Phone          | Email              |
+---------+-----------+----------+----------------+--------------------+
|       1 | Homer     | Connery  | (308) 555-0100 | [email protected]  |
|       2 | Bart      | Pitt     | (231) 465-3497 | [email protected] |
|       3 | Nancy     | Simpson  | (489) 591-0408 | NULL               |
|       4 | Boris     | Trump    | (349) 611-8908 | NULL               |
|       5 | Woody     | Eastwood | (308) 555-0112 | [email protected]  |
|       6 | Burt      | Tyson    | (309) 565-0112 | [email protected]   |
+---------+-----------+----------+----------------+--------------------+

Notice that Owner 2 (Bart) has an email address that contains an underscore.

Here’s an example of what happens when we don’t escape an underscore:

SELECT * FROM Owners
WHERE Email LIKE 'b_%';

Result:

+---------+-----------+----------+----------------+--------------------+
| OwnerId | FirstName | LastName | Phone          | Email              |
+---------+-----------+----------+----------------+--------------------+
|       2 | Bart      | Pitt     | (231) 465-3497 | [email protected] |
|       6 | Burt      | Tyson    | (309) 565-0112 | [email protected]   |
+---------+-----------+----------+----------------+--------------------+

In this case, it matched two rows. In this case, it didn’t matter what the second character was – it matched any character. The fact that the first row contains an underscore for the second character is just a coincidence. It would have matched even if it was something else.

But what if we only wanted to match those email addresses that have an actual underscore as their second character?

That’s where the escape character comes in.

SELECT * FROM Owners
WHERE Email LIKE 'b\_%';

Result:

+---------+-----------+----------+----------------+--------------------+
| OwnerId | FirstName | LastName | Phone          | Email              |
+---------+-----------+----------+----------------+--------------------+
|       2 | Bart      | Pitt     | (231) 465-3497 | [email protected] |
+---------+-----------+----------+----------------+--------------------+

Now we only get one row – the row that contains the underscore as the second character.

Changing the Escape Character

It’s possible to change the escape character. You can do this with the ESCAPE argument.

Example:

SELECT * FROM Owners
WHERE Email LIKE 'b*_%' ESCAPE '*';

Result:

+---------+-----------+----------+----------------+--------------------+
| OwnerId | FirstName | LastName | Phone          | Email              |
+---------+-----------+----------+----------------+--------------------+
|       2 | Bart      | Pitt     | (231) 465-3497 | [email protected] |
+---------+-----------+----------+----------------+--------------------+

In this case I assigned the asterisk (*) as the escape character.

This can be especially handy when you need to use the backslash for other purposes, such as encoding special characters. In such cases, you may find yourself having to “double-escape” these characters, which could become confusing. Changing the escape character can therefore help in such situations.

NULL Values

The percentage (%) wildcard matches anything – almost. One thing it doesn’t match is NULL. 

SELECT * FROM Owners
WHERE Email LIKE '%';

Result:

+---------+-----------+----------+----------------+--------------------+
| OwnerId | FirstName | LastName | Phone          | Email              |
+---------+-----------+----------+----------------+--------------------+
|       1 | Homer     | Connery  | (308) 555-0100 | [email protected]  |
|       2 | Bart      | Pitt     | (231) 465-3497 | [email protected] |
|       5 | Woody     | Eastwood | (308) 555-0112 | [email protected]  |
|       6 | Burt      | Tyson    | (309) 565-0112 | [email protected]   |
+---------+-----------+----------+----------------+--------------------+

In our table, there are two rows with NULL in the Email column, but those aren’t returned here.

Performance

Queries that contain the LIKE operator can run a lot slower than other queries, and you should probably avoid using the LIKE operator unless you really need it. Using the % operator as a prefix can be particularly slow.

That is not to say you shouldn’t use it at all. The LIKE operator is an integral part of SQL, and you’ll encounter many scenarios where it will be the only option (or at least, the best option).

The NOT LIKE Operator

You can also use NOT LIKE to test whether or not a string does not match a pattern.