Summary -

In this topic, we described about the LIKE Condition with detailed example.

LIKE condition is used to fetch matching rows or values from the table that satisfies the wildcard. LIKE condition is used in WHERE clause along with a wildcard. By using wildcards in LIKE condition, we can fetch the satisfied rows from the table. Wildcards have "%" and "_" are used in LIKE condition.

  • % -> specifies zero or more characters.
  • _ -> specifies single character only.

Note :- Wildcards are used to compare the columns that are string based.

LIKE condition uses with SELECT, UPDATE and DELETE statements.

SELECT Syntax –

SELECT column1, column2, ..., columnN
FROM table_name 
[WHERE required_column LIKE pattern]; 

UPDATE Syntax –

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN 
[WHERE required_column LIKE pattern]; 

DELETE Syntax –

DELETE FROM table_name
[WHERE required_column LIKE pattern]; 

  • column1, column2, …, columnN – Specifies the column names from table.
  • table_name – Specifies the name of the table.
  • required_column – Specifies the column that are used to compare with pattern.
  • pattern – Specifies the set of characters with wildcards used to compare with column.

Example -

Let us consider the employee_details as follows –

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Get matching rows or values using LIKE condition.

Requirement1 - Fetch details of employees who joined in October 2019. The query was as follows -

SELECT * FROM employee_details WHERE date_of_hire LIKE '2019-10%'; 

By executing above query, we can get results as shown below -

emp_id emp_name designation manager_id date_of_hire salary dept_id
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement2 - Fetch details of employees whose designation have "anager" from second letter. The query was as follows –

SELECT * FROM employee_details WHERE designation LIKE "_anager"; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000

Requirement3- Fetch details of employees whose designation is end with "man". The query was as follows –

SELECT * FROM employee_details WHERE designation = '%man'; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Requirement4- Fetch details of employees whose designation is start with "Cle" end with "k". The query was as follows –

SELECT * FROM employee_details WHERE designation LIKE 'Cle_k'; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

Requirement5 - Fetch details of employees whose designation have "ana" from second letter and no idea about remaining characters. The query was as follows –

SELECT * FROM employee_details WHERE designation LIKE "_ana%"; 

By executing above query, we can get results as shown below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000

Scenario – Update matching rows or values using LIKE condition.

Requirement - Update manager details as "Employee1" for employees who joined in October 2019. The query was as follows –

UPDATE employee_details
SET manager_id = "Employee1"
WHERE date_of_hire LIKE '2019-10%'; 

By executing above query, we can update the manager_id as "Employee1" who joined in October 2019. The updated table shown as below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee1 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee1 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee1 2019-10-08 13000.00 2000

Scenario – Delete matching rows or values using LIKE condition.

Requirement - Delete employee details whose manager name ends with "yee3". The query was as follows –

DELETE FROM employee_details WHERE manager_id LIKE '%yee3'; 

By executing above query, we can delete the employee rows whose manager_id ends with "yee3". The updated table shown as below –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000