Oracle LIKE operator

Oracle condition LIKE allows to use wildcards which will be used in WHERE operator in SELECT, INSERT, UPDATE or DELETE queries. This allows comparison with a pattern.

LIKE syntax in Oracle/PLSQL

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters and arguments of the condition

• expression – a symbolic expression, such as a field or a column.
• pattern – A symbolic expression that contains a pattern matching. pattern that you can choose:

• escape_character – Optional. Allows checking for literals of wildcards, such as % or _.

Example of % usage (percentage symbol)

The first Oracle example of the LIKE operator that we will look at involves the use of the % wildcard.

Let’s look at how % works in Oracle of the LIKE operator. We want to find all the customers whose last_name starts with ‘Ar’.

SELECT last_name
FROM customers
WHERE last_name LIKE 'Ap%';

You can also use multiple % characters within a single line.

For example:

SELECT last_name
FROM customers
WHERE last_name LIKE '%er%';

In this example of the Oracle LIKE operator, we search for all customers whose last_name contains ‘er’ characters.

Example of using _ (underscore character)

Next, let’s take a look at how the _ (underscore character) wildcard works in the Oracle LIKE operator. Remember that _ only looks for one character.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE 'Sm_th';

In this example Oracle LIKE will return all suppliers whose supplier_name is 5 characters long, where the first two characters are ‘Sm’ and the last two characters are ‘th’. For example it can return suppliers whose supplier_name is ‘Smith’, ‘Smyth’, ‘Smath’ or ‘Smeth’ etc.

Here is another example:

SELECT *
FROM suppliers
WHERE account_number LIKE '92314_';

Looking for an account number, you may find that you have only 5 of 6 digits. In the above example, potentially 10 last entries will be returned (where the missing value may be from 0 to 9). For example, a query may return a supplier whose account_number is:

923140, 923141, 923142, 923143, 923144, 923145, 923146, 923147, 923148, 923149

Example of NOT operator usage

Next, let’s look at how you will use the Oracle NOT operator with wildcards.

Let’s use % with the NOT operator. You can also use Oracle’s LIKE operator to search for suppliers (suppliers) whose names do not start with ‘W’.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name NOT LIKE 'W%';

By placing the NOT operator before LIKE, you can get all suppliers whose supplier_name does not start with ‘W’.

Example of ESCAPE usage

It is important to understand how escape_character works when it matches a pattern. These examples refer specifically to character skipping in Oracle.

Let’s say you want to find % or _ (a percentage character or an underscore) in a LIKE operator. You can do this with ESCAPE characters.

Oracle Tutorial – Like Operator