Oracle LIKE Operator

This SQL tutorial focuses on the Oracle LIKE operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in Oracle. To read additional posts regarding this subject, please use the following links:

• Introduction – Oracle WHERE clause
• Simple comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Oracle LIKE Operator

In Oracle, You may not always know the exact value to search for, sometimes you may want to select rows that match a certain character pattern, for example:

• All customers whose first name starts with ‘M’ (First Name column).
• All employees whose mobile phone starts with 054 (Cell Phone column).
• All vehicles whose license plate ends in 86 (License Plate Number column).

The Oracle LIKE operator is used to perform a wildcard searches and retrieve rows that match a certain character pattern.

SELECT ..
FROM ..
WHERE  column_name  LIKE 'pattern'

Oracle Wildcards Operators

Two symbols can be used to construct the search string:

% – The percent (%) sign, represents any sequence of characters (0 or more).

_ – The underscore (_) sign, represents any single character.

The following Oracle examples demonstrate the usage of these symbols:

SELECT product_name
FROM products
WHERE product_name LIKE '%e'

• ‘%e’ – all of the product names that end in the letter ‘e’. This Oracle LIKE example actually puts no lower or upper limit on the number of letters before the last letter – ‘e’ (any number of characters is allowed), but requires the string to end in the letter ‘e’.
• Values that meet this condition are, for example: ‘Coffee’, ‘Cheese’, ‘Cake’.
• Theoretically, a product whose name is : ‘e’ would have also been included here, because % represents any number of characters, including zero.

SELECT product_name
FROM products
WHERE  product_name LIKE 'S%'

• ‘S%’ – all of the product names that begin in the letter ‘S’. This Oracle LIKE example actually puts no lower or upper limit on the number of letters after the first letter (any number of characters is allowed), but requires the word to begin with the letter ‘S’.
• Values that meet this condition are, for example: ‘Shampoo’, ‘Soap’, ‘Shaving Cream’.
• String values in Oracle are case sensitive; therefore, a product whose name is ‘suger’ (starts with a lowercase ‘s’) would not have been included here.
• Theoretically, a product whose name is  ‘S’ would have also been included here, because % represents any number of characters, including 0.

SELECT product_name
FROM products
WHERE  product_name LIKE '%a%'

• ‘%a%’ – all of the product names that contain the letter ‘a’. This Oracle LIKE example actually puts no lower or upper limit on the number of letters before or after the letter ‘a’ (any number of characters is allowed), but requires the word to contain the letter ‘a’.
• Values that meet this condition are, for example: ‘Soda’, ‘Bagels’, ‘Vegetables’, ‘Pasta’, ‘apples’.
• Theoretically, a product whose name is  ‘a’ would have also been included here, because % represents any number of characters, including 0.

SELECT product_name
FROM products
WHERE  product_name LIKE '_a%'

• ‘_a%’ – all of the product names whose second letter is ‘a’.
• This Oracle LIKE example allows any single character to appear as the first character of the name ( _ stands for a single character). The second character of the name must be equal to ‘a’. After the letter ‘a’, there is no lower or upper limit to the letters that can follow.
• Values that meet this condition are, for example: ‘Pasta’, ‘waffles’ or ‘bagels’.

Oracle NOT Operator

This Oracle operator produce opposite results than those produced by the LIKE operator.

The NOT keyword is written before the LIKE keyword.

This Oracle LIKE example would retrieve all products whose name contains the letter ‘n’ :

SELECT product_name
FROM products
WHERE  product_name LIKE '%n%'

This Oracle LIKE example would retrieve all products whose name does not contain the letter ‘n’ :

SELECT product_name
FROM products
WHERE  product_name NOT LIKE '%n%'