Oracle LIKE

If we want to retrieve some data by matching some text patterns, then we can use the LIKE operator.

Suppose, we want to find all the employees whose first name starts with letter ‘F’ then we can use LIKE operator. We can also find patterns, like names containing letters ‘er’ or names ending with ‘s’.

Syntax for LIKE operator is as follows:

SELECT column_name1, column_name2 … FROM table_name
WHERE column_name LIKE [ 'pattern' ]

Here pattern contains special characters or wildcards for recognizing patterns in text.

% – It is used for recognizing patterns of any length ( including zero length)
_ – It is used for recognizing pattern that matches exactly one character.

These wildcards can be also be used in combination to recognize more patterns.

Lets understand this using some examples.

Oracle LIKE operator examples

We will be using Employees table from HR schema for this example.

1. % Wildcard Example

Suppose we want to find all the employees whose first name starts with the letter ‘f’ then we will use this query.

SELECT first_name, last_name FROM employees
WHERE first_name LIKE 'O%';

Result:

This query returned, an employee name Oliver who is the only employee in the table whose name start with ‘O’.

Similarly, we can use % Wildcard to find patterns, like employees whose name starts with ‘Mi’ then we use this query:

SELECT first_name, last_name 
FROM employees
WHERE first_name LIKE 'Mi%';

Result:

Anything we write in ‘ ‘ is case sensitive. To make our query case-insensitive, we can use LOWER or UPPER string functions.

We can write the same query as:

SELECT first_name, last_name 
FROM employees
WHERE UPPER (first_name) LIKE 'MI%';

Result:

Oracle LIKE % Wildcard with UPPER function

Or as,

SELECT first_name, last_name 
FROM employees
WHERE LOWER (first_name) LIKE 'mi%';

Result:

Oracle LIKE % Wildcard with LOWER function

Using UPPER() and LOWER() functions, we can convert the text into uppercase or lowercase and then match it with our specified input.

2. _ Wildcard Example

We can also use _ wildcard to find patters inside text. Each underscore represents just one character. We can use multiple underscores together for more than one character.

Lets understand this using an example,

Suppose we want to find all the employees in table who have a four letter name that starts with ‘L’ and ends with ‘a’., then we will use this query

SELECT first_name, last_name,salary 
FROM employees
WHERE first_name LIKE 'L__a';

Result:

This query contains two underscores that represent two characters.

3. Combination of _ and % Wildcards with LIKE statement

We can use a combination of % and _ wildcards to find multiple matters in texts. We can see some of the example as follows:

Some of the other combinations can be seen in this table:

4. Escape Clause Examples

Escape Clause is used for exceptions while finding patterns when the pattern itself contains some special characters. We can use Escape clause to exclude all these special characters like ‘% or _’.

Now see this example to understand this better.

Suppose we want to find employees who work in IT department which we can find by querying the values in job_id column. But the values themselves contain ‘_’ underscore wildcard. We want to exclude this wildcard.

For this we use this query,

SELECT first_name, last_name,job_id 
FROM employees
WHERE job_id LIKE 'IT!_%' ESCAPE '!';

Any character that comes after ‘!’ is excluded.

Result:

We can find all the employees who work in IT related departments.

In this tutorial, we learned about the LIKE operator and two special wildcards used for finding patterns. We also learned about ESCAPE clause which is used to exclude characters after the special character we input.

<< Previous
IN

Next >>
IS NULL