LIKE Operator in Oracle

LIKE Operator in Oracle with Examples

In this article, I am going to discuss LIKE Operator in Oracle with Examples. Please read our previous article, where we discussed BETWEEN and NOT BETWEEN Operators in Oracle with Examples. At the end of this article, you will understand LIKE, and NOT LIKE Operators with examples.

What is LIKE Operator in Oracle?

The LIKE Operator in Oracle is used with the WHERE clause to search for a specific pattern in a given string. The string pattern contains wildcard characters that represent missing characters. The LIKE operator is written as a word “LIKE” followed by a pattern to match with column values. Here is an example of LIKE operators in Oracle,

SELECT * FROM Employee WHERE name LIKE ‘%P’; Here the symbol percentage is a wildcard character.

SyntaxExpression LIKE pattern [ESCAPE ‘Escape Character’], where expression is the specific column of the table, the pattern is the character expression that contains pattern matching, and Escape Character is used to check the wildcard characters like % or _.

Understanding LIKE Operator in Oracle:

Let us understand LIKE and NOT LIKE Operators in Oracle with some examples. We are going to use the following Employee table to understand the LIKE and NOT LIKE operators.

LIKE Operator in Oracle with Examples

Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data. We also set the linesize to get the output in the above format.

SET linesize 300;

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');
Understanding Wildcard Characters in Oracle

Before using the LIKE Operator in Oracle, first, we need to understand what are wildcard characters. A wildcard character in Oracle is used as a substitute for any other characters in a string. The wildcards are used to search for data in a given column. The wildcard characters in Oracle are basically used with the LIKE operator. In Oracle, there are two wildcards are as follows.

  1. % Symbol represents any no of characters (zero or more characters) in the expression.
  2. _ will represent a single character in the expression.
Like Operator Using % in Oracle with Examples

The percent (%) means it will search for zero or more characters after the “%”. Here ‘%’ is the wildcard character that we will use before or after characters to search for the required matched string of characters.

Example: Fetch all the Employees whose names start with the letter P.

The following SELECT statement will return all employees from the Employee table whose name starts with the character ‘P’ followed by any number of characters. 

SELECT * FROM Employee WHERE Name LIKE ‘P%’;

Once you execute the above Query, then you will get the following output, and please observe all the employees having the name start with the letter P.

Like Operator Using % in Oracle with Examples

Example: Fetch all the employees whose city ends with ai.

The following SELECT SQL query will return all the employees from the Employee table where the employee City name ends with the string ‘ai’. Here we mentioned the pattern as ‘%ai’. This means returning all records whose city ends with the string ‘ai’.

SELECT * FROM Employee WHERE City LIKE ‘%ai’;

Once you execute the above SQL query, you will get the following result set and please observe all the employees having the city ends with the string ai.

Fetch all the employees whose city ends with ai.

Example: Fetch all employee whose name contains the word am

The following SQL query will return all the employees from the Employee table where the Name containing the word ‘am’ is anywhere in the name column. This is because we have mentioned patterns like ‘%am%’. 

SELECT * FROM Employee WHERE Name LIKE ‘%am%’;

Once you execute the above SQL query, you will get the following Output.

Fetch all employee whose name contains the word am

NOT LIKE Operator in Oracle

We can also revert the SQL statement by using the NOT LIKE operator. Let us understand the use of NOT Like Operator with examples.

Example: Fetch all the employees whose name does not start with P.

The following select statement will return all employees from the Employee table whose name does not start with the character ‘P’. The employee whose starting alphabet is not P will be returning as output. This is just the opposite of the LIKE Operator.

SELECT * FROM Employee WHERE Name NOT LIKE ‘P%’;

Once you execute the above query, you will get the following output, and please observe it return all the employees whose name does not start with the character p.

NOT LIKE Operator in Oracle

Example: Fetch all the employees whose city does not end with ai.

The following SQL query will return all the employees whose city name does not end with the word ‘ai’.

SELECT * FROM Employee WHERE CITY NOT LIKE ‘%ai%’;

Once you execute the above SQL query, you will get the following output which returns all the employees whose city name does not end with the string ai.

NOT LIKE Operator in Oracle with Examples

Example: Fetch all employee whose name does not contain the word am

The following SQL SELECT Statement will return all the employees from the Employee table where the Employee Name does not contain the word ‘am’ anywhere in the name column.

SELECT * FROM Employee WHERE Name NOT LIKE ‘%am%’;

Once you execute the above SQL query, you will get the following result set.

Fetch all employee whose name does not contain the word am

Like Operator Using _ (underscore) Wildcard Character in Oracle

The underscore (_) means it will exactly search for one character after the “_”. Let us understand the use of _ wildcard character with an example. Let’s filter the data more precisely and this time get the employee’s city starting with ‘Londo’ but only containing a total of 6 characters. The following is the SQL statement which will return the same.

SELECT * FROM Employee WHERE City LIKE ‘Londo_’;

Once you execute the above query, you will get the following output. Here the employees with city value contain Londo and are of only 6 characters are returned in the result set.

Like Operator Using _ (underscore) Wildcard Character in Oracle

Example: Fetch all the employees whose department is having two letters

The following SQL query will return all the employees from the Employee table where the Department name is having two characters. Here we are using two underscores (__) as the part in the LIKE Operator.

SELECT * FROM Employee WHERE Department LIKE ‘__’;

Once you execute the above SQL Statement, you will get the following result set which includes only the employees from the IT and HR departments.

Like Operator Using _ (underscore) Wildcard Character in Oracle with Examples

Note: The LIKE Operator is Used to search for a specific pattern in a given input. The % (percentage) and _ (underscore) are two wildcard characters supported by Oracle. The % (percentage) represents the remaining group of characters in the given input. The _ (underscore) represents one character in the given input.

Example: Display all the employees whose names are having the second letter as ‘i’ in the Employee table

SELECT * FROM Employee WHERE Name LIKE ‘_i%’;

Once you execute the above query, you will get the following output.

Display all the employees whose names are having the second letter as'i' in the Employee table

Like operator with special char’s:

Example: Whose employee name is having “#” char?
SELECT * FROM Employee WHERE Name LIKE ‘%#%’;

Example: Whose employee name is having “@” char?
SELECT * FROM Employee WHERE Name LIKE ‘%@%’;

Example: Whose employee name is having “_” char?
SELECT * FROM Employee WHERE Name LIKE ‘%\_%’ESCAPE ‘\’;

Example: Whose employee name is having “%” char?
SELECT * FROM EMP77 WHERE ENAME LIKE ‘%\%%’ESCAPE ‘\’;

Note: Generally oracle database server will treat these symbols (%, _ ) as “wildcard operators” but not “special chars”. To overcome this problem, we must use a pre-defined statement i.e. “escape ‘\’ “.

In the next article, I am going to discuss SET Operators in Oracle with Examples. Here, in this article, I try to explain LIKE Operator in Oracle with Examples and I hope you enjoy this LIKE Operator in Oracle with Examples article. If you have any queries regarding the Oracle Between Operator, then please let us know by putting your query in the comment section.

Leave a Reply

Your email address will not be published.