SQL condition LIKE

The SQL condition LIKE allows you to use wildcards to match the pattern in the query. The LIKE condition is used in the WHERE clause of the SELECT, INSERT, UPDATE or DELETE operator.

Syntax for LIKE condition in SQL

expression_id LIKE pattern_id [ ESCAPE 'escapecharacter_id' ]

where:

• expression_id – Symbolic expression such as a field or column
• pattern_id – Symbolic expression that contains a comparison with the template. Substitution characters that you can select:
• ESCAPE ‘escapecharacter_id’ – It’s optional. This allows you to match templates with literal wildcards such as % or _.

Using % wildcard in LIKE condition

Let’s look at how the % wildcard works in the LIKE SQL condition. Remember that the % wildcard matches any string of any length (including zero length).

In this first example we want to find all entries in the customers table where the l_name of the client starts with ‘C’.

In this example we have a customer table with the following data:

Enter the following SQL statement.

SELECT *
FROM customs
WHERE l_name LIKE 'C%'
ORDER BY l_name;

Two entries will be selected. Here are the results that you should get.

In this example, records of the customers table are returned where last_name starts with ‘C’. As you can see, records by Cruise and Crowe names have been returned.
Since the LIKE condition is case insensitive, the next SQL statement will return the same results.

SELECT *
FROM customs
WHERE l_name LIKE 'c%'
ORDER BY l_name;

Using multiple % wildcards in LIKE condition

You can also use the % wildcard multiple times with the LIKE condition. Using the same table with the following data:

Let’s try to find all the last_name values from the customers table where last_name contains the letter ‘e’.

Enter the following SQL statement:

SELECT l_name
FROM customs
WHERE l_name LIKE '%e%'
ORDER BY l_name;

Three entries will be selected. Here are the results that you should get.

In this example, the names Bieber, Gomez and Depp contain the letter ‘e’.

Using the wildcard _ in the condition LIKE

Next, let’s look at how the _ (underscore character) substitution works in the LIKE condition. Remember that the wildcard _ looks for exactly one character, unlike the % wildcard.

Using the table with the following data:

Let’s try to find all the records from the category table, where cat_id is 2 digits long and ends in ‘5’. Enter the following SQL statement.

SELECT *
FROM cats
WHERE cat_id LIKE '_5';

Two records will be selected. Here are the results you should get.

In this example, there are 2 entries that will match the template – cat_id with values 25 and 75. Note that cat_id equal to 125 was not selected because the symbol _ matches only one character.

Using multiple wildcards _ in LIKE condition

If you want to match a three-digit value ending in “5”, you will need to use the _ double wildcard. You can modify your query as follows.

SELECT *
FROM cats
WHERE cat_id LIKE '__5';

Now you will return cat_id value equal to 125.

Using NOT operator with LIKE condition

Next, let’s look at an example of using the NOT operator with the LIKE condition. In this example, we have a table with the following data:

Let’s take a look at all the entries in the suppliers table where supplier_name does not contain literal ‘o’. Enter the following SQL statement.

SELECT *
FROM suppls
WHERE suppl_name NOT LIKE '%o%';

4 entries will be selected. Here are the results that you should get.

In this example, there are four entries in the suppliers table where suppl_name does not contain literal ‘o’.

Using escape-symbols with LIKE condition

It is important to understand how to use the “escape-symbol” when it matches the template. You can screen % or _ and search for literal versions.

Let’s say you wanted to find % as a literal in the LIKE condition. You can do this with the escape-symbol. In our example we will use ! as an escape-symbol in the LIKE condition.

NOTE: You can only define an escape-character as one character. It is best to choose a character that will not appear in your data very often, for example ! or #.

In this example, we have a test table with the following data:

We could return all entries from the test table where test1_value contains literal %. Enter the following SQL statement.

SELECT *
FROM test1
WHERE test1_value LIKE '%!%%' escape_id '!

Here are the results you should get.

In this example, the ! character is defined as an escape-symbol. The first and last % values in the LIKE condition are treated as normal wildcards. !% is shielded by %, so it is treated as a literal value of %.

You can additionally change the above example and return only those test_values that start with 1 and contain a literal %. Enter the following SQL statement:

SELECT *
FROM test
WHERE test_value LIKE '1%!%%' escape '!

Here are the results you should get.

This example, this time, will only return one record. Because there is only one test_value that starts with 1 and contains literal %.

How to use the SQL LIKE Condition