Oracle LIKE

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 first names end with 'er'. In this case, you use the Oracle LIKE operator.

The syntax of the Oracle LIKE operator is as follows:

expresion [NOT] LIKE pattern [ ESCAPE escape_characters ] Code language: CSS (css)

In this syntax, we have:

expression #

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

pattern #

The pattern is a string to search for in the expression. The pattern includes the following wildcard characters:

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

escape_character #

The escape_character is a character that appears in front of a wildcard character to specify that the wildcard should not be interpreted as a wildcard but as a regular character.

The escape_character, if specified, must be one character and it has no default value.

The LIKE operator returns true if the expression matches the pattern. Otherwise, it returns false.

The NOT operator, if specified, negates the result of the LIKE operator.

Oracle LIKE examples #

Let’s take some examples of using the Oracle LIKE operator to see how it works.

We will use the contacts table in the sample database for the demonstration:

Using % wildcard character #

The following example uses the % wildcard to find the phones of contacts 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: JavaScript (javascript)

Try it

Output:

In this example, we used the pattern:

'St%'Code language: JavaScript (javascript)

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: JavaScript (javascript)

Try it

Output:

The pattern:

%er

matches any string that ends with the 'er' string.

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

UPPER( last_name ) LIKE 'ST%'

LOWER(last_name LIKE 'st%'Code language: JavaScript (javascript)

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: JavaScript (javascript)

Try it

Output:

Using _ 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.

Mixed wildcard characters example #

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: JavaScript (javascript)

Try it

Output:

#

ESCAPE clause examples #

The ESCAPE clause allows you to find strings that include one or more wildcard characters.

For example, a table may include data that has percent % character such as discount values, and depreciation rate.

To search for the string 25%, you use the ESCAPE clause as follows:

LIKE '%25!%%' ESCAPE '!'Code language: JavaScript (javascript)

If you don’t use the ESCAPE clause, Oracle will return any rows with the string 25.

The following statements create discounts table and insert some sample data for testing:

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: PHP (php)

Try it

If you are not familiar with the statements used in this script, you can learn them in the subsequent tutorials.

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:

In this tutorial, you have learned how to use the Oracle LIKE operator to query data that match a specified pattern.