Oracle LIKE Operator

Summary: In this tutorial, you will learn how to use the Oracle LIKE operator to test whether values in a column match a specified pattern.

Introduction to the Oracle LIKE operator #

Sometimes, you want to query data based on a specified pattern. For example, you may want to find contacts whose last names start with 'St' or whose first names end with 'er'. In this case, you can use the Oracle LIKE operator.

Here’s the syntax of LIKE operator:

expression LIKE patternCode language: SQL (Structured Query Language) (sql)

In this syntax:

• The expression is a column name or an expression that you want to test against the pattern.
• The pattern is a string to match.

The pattern includes the following wildcard characters:

• % (percent) matches any string of zero or more characters.
• _ (underscore) matches any single character.

The LIKE operator returns true if the expression matches the pattern or false otherwise.

The NOT operator negates the LIKE operator:

expression NOT LIKE patternCode language: SQL (Structured Query Language) (sql)

The NOT LIKE operator returns true if the expression match pattern or false otherwise.

Oracle LIKE operator examples #

We’ll use the contacts table in the sample database for the demonstration:

Using % wildcard character #

The following example uses the LIKE operator with the % wildcard to find the phones of people whose last names start with 'St':

SELECT
  first_name,
  last_name,
  phone
FROM
  contacts
WHERE
  last_name LIKE 'St%'
ORDER BY
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example, we use the following pattern:

'St%'Code language: SQL (Structured Query Language) (sql)

The LIKE operator matched any string that starts with 'St' and is followed by any number of characters, e.g., Stokes, Stein, or Steele, etc.

To find the phone numbers of contacts whose last names end with the string 'er', you use the following statement:

SELECT
  first_name,
  last_name,
  phone
FROM
  contacts
WHERE
  last_name LIKE '%er'
ORDER BY
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

The following pattern matches any string that ends with the 'er' string.

%erCode language: SQL (Structured Query Language) (sql)

To perform a case-insensitive match, you use LOWER() or UPPER() function:

UPPER( last_name ) LIKE 'ST%'

LOWER(last_name LIKE 'st%'Code language: SQL (Structured Query Language) (sql)

For example, the following statement finds emails of contacts whose first names start with CH:

SELECT
  first_name,
  last_name,
  email
FROM
  contacts
WHERE
  UPPER(first_name) LIKE 'CH%'
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

The following example uses the NOT LIKE operator to find contacts whose phone numbers do not start with '+1':

SELECT
  first_name,
  last_name,
  phone
FROM
  contacts
WHERE
  phone NOT LIKE '+1%'
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Using the _ wildcard character #

The following example finds the phone numbers and emails of contacts whose first names have the following pattern 'Je_i':

SELECT
  first_name,
  last_name,
  email,
  phone
FROM
  contacts
WHERE
  first_name LIKE 'Je_i'
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

The pattern 'Je_i' matches any string that starts with 'Je', followed by one character, and then followed by 'i' e.g., Jeri or Jeni, but not Jenni.

Mixing wildcard characters #

You can mix the wildcard characters in a pattern.

For example, the following statement finds contacts whose first names start with the string Je followed by two characters and then any number of characters. In other words, it will match any last name that starts with Je and has at least 3 characters:

SELECT
  first_name,
  last_name,
  email,
  phone
FROM
  contacts
WHERE
  first_name LIKE 'Je_%';Code language: SQL (Structured Query Language) (sql)

Try it

Output:

#

ESCAPE clause #

The LIKE operator supports an option ESCAPE clause:

expression LIKE pattern ESCAPE escape_characterCode language: SQL (Structured Query Language) (sql)

The escape_character is a character that appears in front of a wildcard character to instruct Oracle to treat the wildcard character as a regular character.

For example, if we use the pattern %25%, the LIKE operator will find a string that starts with zero or more characters, followed by 25, and ends with zero or more characters.

However, we intend to find a string that starts with zero or more characters and is followed by 25%.

To make it work properly, we can use an escape character, such as %20!%%. The character ! serves as an escape character. It instructs Oracle to treat the wildcard (%) that appears immediately after it as a regular character:

LIKE '%25!%%' ESCAPE '!'Code language: SQL (Structured Query Language) (sql)

Note that you can use other characters as escape characters, such as $ and @.

The following statements create discounts table and insert some rows into the table:

CREATE TABLE discounts (
  product_id NUMBER,
  discount_message VARCHAR2 (255) NOT NULL,
  PRIMARY KEY (product_id)
);

INSERT INTO
  discounts (product_id, discount_message)
VALUES
  (1, 'Buy 1 and Get 25% OFF on 2nd ');

INSERT INTO
  discounts (product_id, discount_message)
VALUES
  (2, 'Buy 2 and Get 50% OFF on 3rd ');

INSERT INTO
  discounts (product_id, discount_message)
VALUES
  (3, 'Buy 3 Get 1 free');Code language: SQL (Structured Query Language) (sql)

Try it

The following statement retrieves products that have a discount of 25%:

SELECT
  product_id,
  discount_message
FROM
  discounts
WHERE
  discount_message LIKE '%25!%%' ESCAPE '!';Code language: SQL (Structured Query Language) (sql)

Try it

Output:

Summary #

• Use the Oracle LIKE operator to query data that matches a specified pattern.
• Use the % wildcard to match zero or more characters.
• Use the _ wildcard to match a single character.
• Use the ESCAPE clause to instruct Oracle to treat a wildcard character as a regular character.

Quiz #