LIKE Condition
This Oracle tutorial explains how to use the Oracle LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises.
Description
The Oracle LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.
Syntax
The syntax for the LIKE condition in Oracle/PLSQL 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 patterns 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_character
Optional. It allows you to test for literal instances of a wildcard character such as % or _.
Note
- See also the Oracle REGEXP_LIKE condition.
Example - Using % wildcard (percent sign wildcard)
The first Oracle LIKE example that we will look at involves using the % wildcard (percent sign wildcard).
Let's explain how the % wildcard works in the Oracle LIKE condition. We want to find all of the customers whose last_name begins with 'Ap'.
SELECT last_name
FROM customers
WHERE last_name LIKE 'Ap%';
You can also using the % wildcard multiple times within the same string. For example,
SELECT last_name
FROM customers
WHERE last_name LIKE '%er%';
In this Oracle LIKE condition example, we are looking for all customers whose last_name contains the characters 'er'.
Example - Using _ wildcard (underscore wildcard)
Next, let's explain how the _ wildcard (underscore wildcard) works in the Oracle LIKE condition. Remember that _ wildcard is looking for only one character.
For example:
SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE 'Sm_th';
This Oracle LIKE condition example would return all suppliers whose supplier_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose supplier_name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
Here is another example:
SELECT *
FROM suppliers
WHERE account_number LIKE '92314_';
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:
923140, 923141, 923142, 923143, 923144, 923145, 923146, 923147, 923148, 923149
Example - Using NOT Operator
Next, let's look at how you would use the Oracle NOT Operator with wildcards.
Let's use the % wilcard with the NOT Operator. You could also use the Oracle LIKE condition to find suppliers whose name does not start with 'T'.
For example:
SELECT supplier_name
FROM suppliers
WHERE supplier_name NOT LIKE 'W%';
By placing the NOT Operator in front of the Oracle LIKE condition, you are able to retrieve all suppliers whose supplier_name does not start with 'W'.
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 Oracle 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).
For example:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'Water!%' ESCAPE '!';
This Oracle LIKE condition example identifies the ! character as an escape character. This statement will return all suppliers whose name is Water%.
Here is another more complicated example using escape characters in the Oracle LIKE condition.
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!%' ESCAPE '!';
This Oracle 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 Oracle LIKE condition.
For example:
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!_' ESCAPE '!';
This Oracle 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 Oracle 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 SELECT statements:
SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE ('TEST%');
OR
SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE UPPER('test%')
These SELECT statements use a combination of the Oracle UPPER function and the 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".
CREATE TABLE employees
( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
Solution for Practice Exercise #1:
The following SELECT statement uses the Oracle LIKE condition to return the records whose employee_name ends with the letter "h".
SELECT *
FROM employees
WHERE employee_name LIKE '%h';
It would return the following result set:
|
EMPLOYEE_NUMBER |
EMPLOYEE_NAME |
SALARY |
|
1001 |
John Smith |
62000 |
|
1004 |
Jack Horvath |
42000 |
Practice Exercise #2:
Based on the employees table populated with the following data, find all records whose employee_name contains the letter "s".
CREATE TABLE employees
( employee_number number(10) not null,
employee_name varchar2(50) not null,
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);
INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);
Solution for Practice Exercise #2:
The following Oracle SELECT statement would use the Oracle LIKE condition to return the records whose employee_name contains the letter "s".
SELECT *
FROM employees
WHERE employee_name LIKE '%s%';
It would return the following result set:
|
EMPLOYEE_NUMBER |
EMPLOYEE_NAME |
SALARY |
|
1002 |
Jane Anderson |
57500 |
|
1003 |
Brad Everest |
71000 |
Practice Exercise #3:
Based on the suppliers table populated with the following data, find all records whose supplier_id is 4 digits and starts with "500".
CREATE TABLE suppliers
( supplier_id varchar2(10) not null,
supplier_name varchar2(50) not null,
city varchar2(50),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);
INSERT INTO suppliers(supplier_id, supplier_name, city)
VALUES ('5008', 'Microsoft', 'New York');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5009', 'IBM', 'Chicago');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5010', 'Red Hat', 'Detroit');
INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5011', 'NVIDIA', 'New York');
Solution for Practice Exercise #3:
The following Oracle SELECT statement would use the Oracle LIKE condition to return the records whose supplier_id is 4 digits and starts with "500".
SELECT *
FROM suppliers
WHERE supplier_id LIKE '500_';
It would return the following result set:
|
SUPPLIER_ID |
SUPPLIER_NAME |
CITY |
|
5008 |
Microsoft |
New York |
|
5009 |
IBM |
Chicago |