totn SQL

SQL: LIKE Condition

This SQL tutorial explains how to use the SQL LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises.

Description

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

subscribe button Subscribe

Syntax

The syntax for the LIKE condition in SQL is:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression
A character expression such as a column or field.
pattern

A character expression that contains pattern matching. The wildcards that you can choose from are:

Wildcard Explanation
% Allows you to match any string of any length (including zero length)
_ Allows you to match on a single character
ESCAPE 'escape_character'
Optional. It allows you to pattern match on literal instances of a wildcard character such as % or _.
TIP: If you are pattern matching with char datatypes, remember that chars are padded with spaces at the end to fill the length of the field. This may give you unexpected results when you use the LIKE condition to pattern match at the end of a string.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using % Wildcard in the LIKE Condition

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

In this first example, we want to find all of the records in the customers table where the customer's last_name begins with 'J'.

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

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Enter the following SQL statement:

Try It
SELECT *
FROM customers
WHERE last_name LIKE 'J%'
ORDER BY last_name;

There will be 2 records selected. These are the results that you should see:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
9000 Johnson Derek techonthenet.com

This example returns the records in the customers table where the last_name starts with 'J'. As you can see, the records for the last names Jackson and Johnson have been returned.

Because the LIKE condition is not case-sensitive, the following SQL statement would return the same results:

Try It
SELECT *
FROM customers
WHERE last_name LIKE 'j%'
ORDER BY last_name;

Using Multiple % Wildcards in the LIKE Condition

You can also using the % wildcard multiple times with the LIKE condition.

Using the same customers table with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Let's try to find all last_name values from the customers table where the last_name contains the letter 'e'. Enter the following SQL statement:

Try It
SELECT last_name
FROM customers
WHERE last_name LIKE '%e%'
ORDER BY last_name;

There will be 3 records selected. These are the results that you should see:

last_name
Anderson
Ferguson
Reynolds

In this example, the last names Anderson, Ferguson and Reynolds contain the letter 'e'.

Example - Using _ Wildcard in the LIKE Condition

Next, let's explain how the _ wildcard (underscore wildcard) works in the LIKE condition. Remember that _ wildcard is looking for exactly one character, unlike the % wildcard.

Using the categories table with the following data:

category_id category_name
25 Deli
50 Produce
75 Bakery
100 General Merchandise
125 Technology

Let's try to find all records from the categories table where the category_id is 2-digits long and ends with '5'. Enter the following SQL statement:

Try It
SELECT *
FROM categories
WHERE category_id LIKE '_5';

There will be 2 records selected. These are the results that you should see:

category_id category_name
25 Deli
75 Bakery

In this example, there are 2 records that will pattern match - the category_id values 25 and 75. Notice that the category_id of 125 was not selected because, the _ wilcard matches only on a single character.

Using Multiple _ Wildcards in the LIKE Condition

If you wanted to match on a 3-digit value that ended with '5', you would need to use the _ wildcard two times. You could modify your query as follows:

Try It
SELECT *
FROM categories
WHERE category_id LIKE '__5';

Now you will return the category_id value of 125:

category_id category_name
125 Technology

Example - Using the NOT Operator with the LIKE Condition

Next, let's look at an example of how to use the NOT Operator with the LIKE condition.

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

supplier_id supplier_name city state
100 Microsoft Redmond Washington
200 Google Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

Let's look for all records in the suppliers table where the supplier_name does not contain the letter 'o'. Enter the following SQL statement:

Try It
SELECT *
FROM suppliers
WHERE supplier_name NOT LIKE '%o%';

There will be 1 record selected. These are the results that you should see:

supplier_id supplier_name city state
400 Kimberly-Clark Irving Texas

In this example, there is only one record in the suppliers table where the supplier_name does not contain the letter 'o'.

Example - Using Escape Characters with the LIKE Condition

It is important to understand how to "Escape Characters" when pattern matching. You can escape % or _ and search for the literal versions instead.

Let's say you wanted to search for % as a literal in the LIKE condition. You can do this using an Escape character. In our example, we will use ! as the escape character in the LIKE condition.

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

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

test_id test_value
1 10%
2 25%
3 100
4 99

We could return all records from the test table where the test_value contains the % literal. Enter the following SQL statement:

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

These are the results that you should see:

test_id test_value
1 10%
2 25%

This example identifies the ! character as an escape character. The first and last % values in the LIKE condition are treated as regular wildcards. The !% is an escaped % so it is treated as a literal % value.

You could further modify the above example and only return test_values that start with 1 and contain the % literal. Enter the following SQL statement:

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

These are the results that you should see:

test_id test_value
1 10%

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

Frequently Asked Questions

Question: How do you incorporate the Oracle UPPER function with the SQL LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.

Answer: To answer this question, let's look at an example.

Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.

If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SQL SELECT statements:

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE ('TEST%');

OR

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE UPPER('test%')

These SQL SELECT statements use a combination of the Oracle UPPER function and the SQL LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.

Practice Exercises

If you want to test your skills using the SQL LIKE condition, try some of our practice exercises.

These exercises allow you to try out your skills with the LIKE condition. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!

Go to Practice Exercises