In SQL, the LIKE operator allows you to do pattern matching. It determines whether a specific character string matches a specified pattern.

A pattern can include regular characters and wildcard characters.

Source Table

The following table is used for the examples on this page.

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]  |
+-----------+-------------+------------+----------------+-------------------+

Example

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

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE Email LIKE '%.com';

Result:

+-------------+------------+-------------------+
| FirstName   | LastName   | Email             |
|-------------+------------+-------------------|
| Homer       | Connery    | [email protected] |
| Bart        | Pitt       | [email protected]  |
| Woody       | Eastwood   | [email protected] |
+-------------+------------+-------------------+

In this example, I used the LIKE operator in conjunction with the % wildcard operator to return owners whose email addresses end in .com.

LIKE can be used with other string operators too, such as _, [], and [^].

The % Wildcard Operator

The % wildcard operator 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.

It tends to be more commonly used as a suffix, and it has limited use in the middle of patterns, although there are some valid use-cases for using it in the middle of a pattern, such as the following:

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE Email LIKE 'bart@%.com';

Result:

+-------------+------------+------------------+
| FirstName   | LastName   | Email            |
|-------------+------------+------------------|
| Bart        | Pitt       | [email protected] |
+-------------+------------+------------------+

In this case, we didn’t know bart’s email address, but we knew it started with bart@ and ended in .com. Therefore, we were able to use LIKE in conjunction with the % wildcard operator to fill in the rest.

Bear in mind that this technique can return a lot of irrelevant rows, depending on the data, and how you construct your SQL statement.

The _ Wildcard Operator

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

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE FirstName LIKE 'b_rt';

Result:

+-------------+------------+------------------+
| FirstName   | LastName   | Email            |
|-------------+------------+------------------|
| Bart        | Pitt       | [email protected] |
| Burt        | Tyson      | [email protected] |
+-------------+------------+------------------+

The [] Wildcard Operator

The brackets ([]) wildcard operator matches any single character within the specified range or set that is specified between the brackets.

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE FirstName LIKE '[bh]%';

Result:

+-------------+------------+-------------------+
| FirstName   | LastName   | Email             |
|-------------+------------+-------------------|
| Homer       | Connery    | [email protected] |
| Bart        | Pitt       | [email protected]  |
| Boris       | Trump      | NULL              |
| Burt        | Tyson      | [email protected]  |
+-------------+------------+-------------------+

Here, I combined the brackets wildcard with the percentage sign to return all owners whose first name starts with either b or h.

Note that the brackets operator does not have widespread adoption in DBMSs. It is supported in SQL Server, but it isn’t supported in MySQL, Oracle, DB2, and SQLite. Check your DBMS documentation to see if it supports this operator.

The [^] Wildcard Operator

The caret character (^) can be used to negate the results when using the brackets operator.

So, we change the previous example to only return those owners whose first name does not start with b or h.

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE FirstName LIKE '[^bh]%';

Result:

+-------------+------------+-------------------+
| FirstName   | LastName   | Email             |
|-------------+------------+-------------------|
| Nancy       | Simpson    | NULL              |
| Woody       | Eastwood   | [email protected] |
+-------------+------------+-------------------+

NULL Values

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

SELECT 
    FirstName,
    LastName,
    Email
FROM Owners
WHERE Email LIKE '%';

Result:

+-------------+------------+-------------------+
| FirstName   | LastName   | Email             |
|-------------+------------+-------------------|
| Homer       | Connery    | [email protected] |
| Bart        | Pitt       | [email protected]  |
| Woody       | Eastwood   | [email protected] |
| Burt        | Tyson      | [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).