SQL LIKE Condition

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.

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

---