Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle LIKE

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 ]

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 character.
  • _ (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 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:

contacts table

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, phone FROM contacts WHERE last_name LIKE 'St%' ORDER BY last_name;

The following picture illustrates the result:

Oracle LIKE - contact last names start with St

In this example, we used the pattern:

'St%'

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;

Here is the result:

Oracle LIKE - contact last names end with er

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

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;

Here is the result:

Oracle LIKE - contact last names case-insensitive match

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;

The result is:

Oracle NOT LIKE example

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, phone FROM contacts WHERE first_name LIKE 'Je_i' ORDER BY first_name;

Here is the result:

Oracle LIKE - _ wildcard example

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, phone FROM contacts WHERE first_name LIKE 'Je_%';

Oracle LIKE - mixed wildcard example

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, depreciation rate.

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

LIKE '%25!%%' ESCAPE '!'

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');

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 which have discount 25%:

SELECT product_id, discount_message FROM discounts WHERE discount_message LIKE '%25!%%' ESCAPE '!';

The result is as follows:

Oracle LIKE - discount example

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

  • Was this tutorial helpful?
  • YesNo
Previous Oracle BETWEEN
Next Oracle IS NULL

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.