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.

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	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following SQL statement:

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	google.com
9000	Johnson	Derek	google.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:

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	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.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:

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:

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:

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:

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.