SQL: LIKE Condition
This SQL tutorial explains how to use the SQL LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises.
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.
Parameters or Arguments
Allows you to match on a single character
escape_character Optional. It allows you to test for literal instances of a wildcard character such as % or _.
Example - Using % wildcard (percent sign wildcard)
The first SQL LIKE example that we will look at involves using the % wildcard (percent sign wildcard).
Let's explain how the % wildcard works in the SQL LIKE condition. We want to find all of the suppliers whose name begins with 'Hew'.
You can also using the % wildcard multiple times within the same string. For example,
In this SQL LIKE condition example, we are looking for all suppliers whose name contains the characters 'bob'.
Example - Using _ wildcard (underscore wildcard)
Next, let's explain how the _ wildcard (underscore wildcard) works in the SQL LIKE condition. Remember that _ wildcard is looking for only one character.
This SQL LIKE condition example would return all customers whose last_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return customers whose last_name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
Here is another example:
You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:
123170, 123171, 123172, 123173, 123174, 123175, 123176, 123177, 123178, 123179
Example - Using the NOT Operator
Next, let's look at how you would use the SQL NOT Operator with wildcards.
Let's use the % wilcard with the NOT Operator. You could also use the SQL LIKE condition to find suppliers whose name does not start with 'T'.
By placing the NOT Operator in front of the SQL LIKE condition, you are able
to retrieve all suppliers whose supplier_name does not start with 'T'.
Example - Using Escape Characters
It is important to understand how to "Escape Characters" when pattern matching. These examples deal specifically with escaping characters in Oracle.
Let's say you wanted to search for a % or a _ character in the SQL LIKE condition. You can do this using an Escape character.
Please note that you can only define an escape character as a single character (length of 1).
This SQL LIKE condition example identifies the. character as an escape character. This statement will return all suppliers whose name is %.
Here is another more complicated example using escape characters in the SQL LIKE condition.
This SQL LIKE condition example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.
You can also use the escape character with the _ character in the SQL LIKE condition.
This SQL LIKE condition example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
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:
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 Exercise #1:
Based on the employees table populated with the following data, find all records whose employee_name ends with the letter "h".
Solution for Practice Exercise #1:Source: www.techonthenet.com