Using LIKE Condition in Oracle

This article is written about how to use the Oracle LIKE condition (to operate pattern matching) with syntax, examples, and practice exercises.

Description

The Oracle LIKE condition permits wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This permits you to function pattern matching.

Syntax

The syntax for the LIKE circumstance in Oracle/PLSQL is:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression A persona expression such as a column or field. pattern A character expression that contains pattern matching. The patterns that you can select from are: Wildcard Explanation % Allows you to in shape any string of any size (including zero length) _ Allows you to in shape on a single character escape_character Optional. It permits you to test for literal situations of a wildcard persona such as percent or _.

Note

See also the Oracle REGEXP_LIKE condition.

Example – Using % wildcard (percent signal wildcard)

The first Oracle LIKE example that we will appear at includes using the p.c wildcard (percent signal wildcard).

Let’s provide an explanation for how the percent wildcard works in the Oracle LIKE condition. We want to discover all of the clients whose last_name starts offevolved with ‘Ap’.

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

You can also using the % wildcard multiple instances inside the equal string. For example,

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

In this Oracle LIKE circumstance example, we are searching for all customers whose last_name carries the characters ‘er’.

Example – Using _ wildcard (underscore wildcard)

Next, let’s give an explanation for how the _ wildcard (underscore wildcard) works in the Oracle LIKE condition. Remember that _ wildcard is looking for only one character.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE 'Sm_th';

This Oracle LIKE situation instance would return all suppliers whose supplier_name is 5 characters long, where the first two characters is ‘Sm’ and the last two characters is ‘th’. For example, it may want to return suppliers whose supplier_name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’, etc.

Here is another example:

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

You might find that you are looking for an account number, however you only have 5 of the 6 digits. The example above, would retrieve doubtlessly 10 data lower back (where the missing fee could equal anything from zero to 9). For example, it ought to return suppliers whose account numbers are:

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

Example – Using NOT Operator

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

Let’s use the percent wilcard with the NOT Operator. You may want to additionally use the Oracle LIKE circumstance to discover suppliers whose title does now not start with ‘T’.

For example:

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

By putting the NOT Operator in the front of the Oracle LIKE condition, you are capable to retrieve all suppliers whose supplier_name does no longer begin with ‘W’.

Example – Using Escape Characters

It is important to recognize how to “Escape Characters” when pattern matching. These examples deal particularly with escaping characters in Oracle.

Let’s say you wanted to search for a percent or a _ persona in the Oracle LIKE condition. You can do this the use of an Escape character.

Please note that you can only outline an get away persona as a single character (length of 1).

For example:

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'Water!%' ESCAPE '!';

This Oracle LIKE circumstance instance identifies the ! personality as an break out character. This announcement will return all suppliers whose identify is Water%.

Here is some other greater complicated example the usage of escape characters in the Oracle LIKE condition.

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!%' ESCAPE '!';

This Oracle LIKE circumstance example returns all suppliers whose identify starts offevolved with H and ends in %. For example, it would return a cost such as ‘Hello%’.

You can additionally use the break out persona with the _ character in the Oracle LIKE condition.

For example:

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!_' ESCAPE '!';

This Oracle LIKE condition example returns all suppliers whose name starts with H and ends in _. For example, it would return a fee such as ‘Hello_’.

Frequently Asked Questions

Question: How do you include the Oracle UPPER feature with the Oracle LIKE condition? I’m making an attempt to question against a free text field for all files containing the word “test”. The hassle is that it can be entered in the following ways: TEST, Test, or test.

Answer: To answer this question, let’s appear at an example.

Let’s say that we have a suppliers desk with a discipline called supplier_name that includes the values TEST, Test, or test.

If we desired to discover all data containing the word “test”, regardless of whether it was stored as TEST, Test, or test, we should run both of the following SELECT statements:

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE ('TEST%');

OR

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE UPPER('test%')

These SELECT statements use a combination of the Oracle UPPER characteristic and the LIKE situation to return all of the files where the supplier_name subject includes the word “test”, regardless of whether or not it used to be saved as TEST, Test, or test.

Practice Exercise #1:

Based on the personnel table populated with the following data, find all archives whose employee_name ends with the letter “h”.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);

Solution for Practice Exercise #1:

The following SELECT declaration makes use of the Oracle LIKE situation to return the archives whose employee_name ends with the letter “h”.

SELECT *
FROM employees
WHERE employee_name LIKE '%h';

It would return the following result set:

EMPLOYEE_NUMBER EMPLOYEE_NAME SALARY 1001 John Smith 62000 1004 Jack Horvath 42000

Practice Exercise #2:

Based on the employees desk populated with the following data, discover all files whose employee_name includes the letter “s”.

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);

Solution for Practice Exercise #2:

The following Oracle SELECT statement would use the Oracle LIKE circumstance to return the archives whose employee_name carries the letter “s”.

SELECT *
FROM employees
WHERE employee_name LIKE '%s%';

It would return the following result set:

EMPLOYEE_NUMBER EMPLOYEE_NAME SALARY 1002 Jane Anderson 57500 1003 Brad Everest 71000

Practice Exercise #3:

Based on the suppliers desk populated with the following data, discover all documents whose supplier_id is 4 digits and begins with “500”.

CREATE TABLE suppliers
( supplier_id varchar2(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers(supplier_id, supplier_name, city)
VALUES ('5008', 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5009', 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5010', 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES ('5011', 'NVIDIA', 'New York');

Solution for Practice Exercise #3:

The following Oracle SELECT announcement would use the Oracle LIKE circumstance to return the documents whose supplier_id is four digits and starts offevolved with “500”.

SELECT *
FROM suppliers
WHERE supplier_id LIKE '500_';

It would return the following result set:

SUPPLIER_ID SUPPLIER_NAME CITY 5008 Microsoft New York 5009 IBM Chicago