Oracle LIKE Operator: Querying data Based on a Specified Pattern (2024)

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:

1) expression

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

2) 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.

3) 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:

A) % wildcard character examples

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

SELECT first_name, last_name, phoneFROM contactsWHERE last_name LIKE 'St%'ORDER BY last_name;Code language: JavaScript (javascript)

The following picture illustrates the result:

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, phoneFROM contactsWHERE last_name LIKE '%er'ORDER BY last_name;Code language: JavaScript (javascript)

Here is the result:

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, emailFROM contactsWHERE UPPER( first_name ) LIKE 'CH%';ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Here is the result:

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

SELECT first_name, last_name, phoneFROM contactsWHERE phone NOT LIKE '+1%'ORDER BY first_name;Code language: JavaScript (javascript)

The result is:

B) _ wildcard character examples

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, phoneFROM contactsWHERE first_name LIKE 'Je_i'ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Here is the result:

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.

C) 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, phoneFROM contactsWHERE first_name LIKE 'Je_%';Code language: JavaScript (javascript)

D) 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)

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%:

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

The result is as follows:

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