syntax and example of like condition in oracle sql

The LIKE condition in Oracle SQL / PLSQL is used in WHERE clause to place wildcard characters while fetching records.
LIKE condition can be used with SELECT, INSERT, UPDATE and DELETE in SQL statements.

LIKE condition have 2 flavors:
1. % – allows us to match string of any length including zero length.
2. _ allows us to match only a single character.

Syntax for the LIKE condition in Oracle SQL / PLSQL is:

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE condition;

Or

INSERT INTO table_name
VALUES(column_name1
,column_name2
,column_name3
.
.
column_nameN)
WHERE column_name LIKE condition;

Or

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE column_name LIKE condition;

Or

DELETE FROM table_name
WHERE column_name LIKE condition;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

Using ‘%’ in LIKE condition
Suppose we want to see the records of employee(s) who work in departments that starts with ‘S’.

The above can be achieved as:

SELECT *
FROM employee
WHERE department LIKE 'S%';

The result of the above query will be:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Here we have retrieved the records pertaining to employee(s) who work in departments starting with ‘S’ using LIKE condition in WHERE clause.

Note that the % sign after ‘S’ in query acts as a wildcard character for String of any length.

Scenario 2:

Using ‘_’ in LIKE condition
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
114	Emp E	32000	Sales	10

Suppose we want to see the records of employee(s) whose ‘employee_id’ is of 3 characters which starts with ‘1’ and ends with ‘4’

We can achieve the same as:

SELECT *
FROM employee
WHERE employee_id LIKE '1_4';

Once we have run the above code following will be the result:

Employee_ID	Employee_Name	Salary	Department	Commission
104	Emp D	30000	Support
114	Emp E	32000	Sales	10

Here we have successfully retrieved the records pertaining to employee whose ‘employee_id’ has 3 characters that starts with ‘1’ and ends with ‘4’ using LIKE condition in WHERE clause.