fastread homefastrread library fastread menu

SQL : LIKE Condition

Tutorial by:Sweta      Date: 2016-04-14 23:05:11

❰ Previous Next ❱

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. The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the SQL LIKE Condition 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 for yourself!

Example - Using % wildcard (percent sign wildcard)

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'.

We have a table called customers with the following data in this example:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE

-----------  ---------  ----------  ---------------------

       4000  Jackson    Joe         www.techonthenet.com

       5000  Smith      Jane        www.digminecraft.com

       6000  Ferguson   Samantha    www.bigactivities.com

       7000  Reynolds   Allen       www.checkyourmath.com

       8000  Anderson   Paige

       9000  Johnson    Derek       www.techonthenet.com

Enter the following SQL statement:

SELECT *

FROM customers

WHERE last_name LIKE 'J%';

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE

-----------  ---------  ----------  ---------------------

       4000  Jackson    Joe         www.techonthenet.com

       9000  Johnson    Derek       www.techonthenet.com

In this example, there are 2 records in the customers table where the last_name starts with 'J'.

Using Multiple % Wildcards

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         www.techonthenet.com

       5000  Smith      Jane        www.digminecraft.com

       6000  Ferguson   Samantha    www.bigactivities.com

       7000  Reynolds   Allen       www.checkyourmath.com

       8000  Anderson   Paige

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

SELECT last_name

FROM customers

WHERE last_name LIKE '%e%';

These are the results that you should see:

LAST_NAME

---------

Ferguson

Reynolds

Anderson

In this example, there are 3 last_name values that contain the letter 'e'.

Example - Using _ wildcard (underscore wildcard)

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.

SUPPLIER_ID  SUPPLIER_NAME  ACCT_NUMBER

-----------  -------------  -----------

        100  Microsoft      123170 

        200  RIM            398745

        300  Oracle         123175

        400  Google         693479

        500  Intel          123178

        600  Samsung        239003

Let's try to find all acct_number values from the suppliers table where acct_number starts with '12317' and has one more digit after. Enter the following SQL statement:

SELECT acct_number

FROM suppliers

WHERE acct_number LIKE '12317_';

These are the results that you should see:

ACCT_NUMBER

-----------

123170

123175

123178

In this example, there are 3 records that have an acct_number value that is 6 characters long and start with '12317'.

Example - Using the NOT Operator

Next, let's see how you would use the NOT Operator with the LIKE condition.

Let's use our customers table again for this example:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE

-----------  ---------  ----------  ---------------------

       4000  Jackson    Joe         www.techonthenet.com

       5000  Smith      Jane        www.digminecraft.com

       6000  Ferguson   Samantha    www.bigactivities.com

       7000  Reynolds   Allen       www.checkyourmath.com

       8000  Anderson   Paige

       9000  Johnson    Derek       www.techonthenet.com

And this time, let's look for all records in the customers table where the last_name does not start with 'J'. Enter the following SQL statement:

SELECT *

FROM customers

WHERE last_name NOT LIKE 'J%';

These are the results that you should see:

CUSTOMER_ID  LAST_NAME  FIRST_NAME  FAVORITE_WEBSITE

-----------  ---------  ----------  ---------------------

       5000  Smith      Jane        www.digminecraft.com

       6000  Ferguson   Samantha    www.bigactivities.com

       7000  Reynolds   Allen       www.checkyourmath.com

       8000  Anderson   Paige

In this example, there are 4 last_name values that do not start with 'J'.

Example - Using Escape Characters

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 LIKE condition 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 LIKE condition 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.

 

❰ Previous Next ❱