Basic about LIKE Condition – using and example

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

Description

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

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 personality expression that contains sample matching. The wildcards that you can select from are: Wildcard Explanation % Allows you to fit any string of any size (including zero length) _ Allows you to match on a single character ESCAPE ‘escape_character’ Optional. It permits you to sample suit on literal cases of a wildcard character such as percent or _.

DDL/DML for Examples

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

Get DDL/DML

Example – Using % Wildcard in the LIKE Condition

Let’s give an explanation for how the p.c wildcard works in the SQL LIKE condition. Remember that the percent wildcard fits any string of any size (including zero length).

In this first example, we favor to discover all of the archives in the customers desk the place the customer’s last_name starts offevolved with ‘J’.

In this example, we have a desk known as clients 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 files selected. These are the results that you have to see:

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

This example returns the archives in the clients table the place the last_name begins with ‘J’. As you can see, the archives for the closing names Jackson and Johnson have been returned.

Because the LIKE circumstance is no longer case-sensitive, the following SQL statement would return the identical results:

Try It

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

Using Multiple % Wildcards in the LIKE Condition

You can additionally the use of the p.c wildcard a couple of times with the LIKE condition.

Using the equal 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 locate all last_name values from the customers desk where the last_name incorporates 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 three files selected. These are the results that you have to see:

last_name

Anderson

Ferguson

Reynolds

In this example, the remaining names Anderson, Ferguson and Reynolds incorporate the letter ‘e’.

Example – Using _ Wildcard in the LIKE Condition

Next, let’s provide an explanation for how the _ wildcard (underscore wildcard) works in the LIKE condition. Remember that _ wildcard is searching for exactly one character, in contrast to the p.c wildcard.

Using the categories desk with the following data:

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

Let’s attempt to find all data from the categories table where the category_id is 2-digits lengthy and ends with ‘5’. Enter the following SQL statement:

Try It

SELECT *
FROM categories
WHERE category_id LIKE '_5';

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

category_id category_name

25 Deli

75 Bakery

In this example, there are 2 archives that will sample healthy – the category_id values 25 and 75 Notice that the category_id of one hundred twenty five was no longer chosen because, the _ wilcard fits only on a single character.

Using Multiple _ Wildcards in the LIKE Condition

If you wanted to fit on a 3-digit cost that ended with ‘5’, you would need to use the _ wildcard two times. You may want to modify your question 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 referred to as 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 no longer comprise 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 consequences that you ought to see:

supplier_id supplier_name metropolis 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 essential to understand how to “Escape Characters” when pattern matching. You can escape percent or _ and search for the literal variations instead.

Let’s say you desired 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 break out persona in the LIKE condition.

NOTE: You can solely outline an escape persona as a single character. It is fine to choose a character that will now not show up in your data very frequently such as ! or #.

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

test_id test_value

1 10%

2 25%

3 100

4 99

We ought to return all archives from the test table the place the test_value contains the p.c 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 instance identifies the ! personality as an get away character. The first and final percent values in the LIKE condition are treated as normal wildcards. The !% is an escaped percent so it is dealt with as a literal percent value.

You may want to further adjust the above example and solely return test_values that begin with 1 and incorporate the percent 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 offevolved with 1 and incorporates the p.c literal.

Frequently Asked Questions

Question: How do you incorporate the Oracle UPPER feature with the SQL LIKE condition? I’m making an attempt to query against a free text discipline for all archives containing the phrase “test”. The hassle is that it can be entered in the following ways: TEST, Test, or test.

Answer: To reply this question, let’s appear 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 desired to discover all files containing the phrase “test”, regardless of whether or not it used to be saved as TEST, Test, or test, we should run both 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 circumstance to return all of the files where the supplier_name discipline includes the phrase “test”, regardless of whether or not it used to be saved as TEST, Test, or test.

Practice Exercises

If you prefer to take a look at your skills the usage of the SQL LIKE condition, attempt some of our practice exercises.

These exercises permit you to try out your competencies with the LIKE condition. You will be given questions that you want to solve. After every exercise, we supply the solution so you can check your answer. Give it a try!

Go to Practice Exercises