LIKE Operator in SQL

Logical Operators in SQL are used to connect two or more expressions. In this article, we will discuss one of the logical operators i.e. LIKE operator in detail about how and where to use them.

Table of Content

• What is the LIKE operator?
  • Syntax
• Wildcard Characters used with LIKE
• Wildcard Characters and ILIKE

What is the LIKE Operator?

LIKE operators in SQL are logical operators that allow determining whether a specific character string matches specific patterns. LIKE operator is case-sensitive.

It includes regular and wildcard characters during pattern matching:

• Regular character must match the character specified in the character string
• Wildcard can be matched with an arbitrary segment of the character string
  • There are two wildcard characters that are very often used with the LIKE operator
    • Percentage (%) – allows to match any string of any length (including zero characters)
    • Underscore (_)  – allows to match on a single character

Must Read: SQL IN

Must Read: SQL BETWEEN

Syntax:

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

Wildcard Characters used with LIKE

Wildcard Character	Description
LIKE ‘n%’	Find the values that start with n
LIKE ‘%g’	Find the values that end with g
LIKE ‘%learn%’	Find the value that has to learn in any position
LIKE ‘_n%’	Find the letter that has n at the second position
LIKE ‘n_%’	Find the word that starts with n but has at least two characters in length
LIKE ‘n%g’	Find the word that starts with n and ends with g

Now, let’s take some example to get a better understand of how to use LIKE operator in SQL.

Here, we will take the Employee Dataset (that contains Employee ID, Name, Gender, Department, Education, Month of Joining, and CTC) to do some examples to get a clear understanding of different logical operators.

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1001	Ajay	M	Engineering	Doctoral	January	25
1002	Babloo	M	Engineering	UG	February	23
1003	Chhavi	F	HR	P	March	15
1004	Dheeraj	M	HR	UG	January	12
1005	Evina	F	Marketing	UG	March	16
1006	Fredy	M	Sales	UG	December	10
1007	Garima	F	Sales	PG	March	10
1008	Hans	M	Admin	PG	November	8
1009	Ivanka	F	Admin	Intermediate	June	7
1010	Jai	M	Peon		December	4

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Example 1: Find the detail of employee whose name starts with G.

Query

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1007	Garima	F	Sales	PG	March	10

Must Read: Subqueries in SQL

Must Read: SQL CREATE TABLE

Example 2: Find the detail of employee whose name ends with y.

Query

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1001	Ajay	M	Engineering	Doctoral	January	25
1006	Fredy	M	Sales	UG	December	10

Must Read: SQL WHERE

Must Read: Introduction to Normalization

Example 3: Find the details of the employee that contains the word “in” at any position in the department.

Query

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1001	Ajay	M	Engineering	Doctoral	January	25
1002	Babloo	M	Engineering	UG	February	23
1005	Evina	F	Marketing	UG	March	16
1008	Hans	M	Admin	PG	November	8
1009	Ivanka	F	Admin	Intermediate	June	7

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

Example 4: Find the employee detail that contains the word ‘G’ at the second position in Education.

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1002	Babloo	M	Engineering	UG	February	23
1003	Chhavi	F	HR	P	March	15
1004	Dheeraj	M	HR	UG	January	12
1005	Evina	F	Marketing	UG	March	16
1006	Fredy	M	Sales	UG	December	10
1007	Garima	F	Sales	PG	March	10
1008	Hans	M	Admin	PG	November	8

Example 5: Find the name and education of employees for which the education starts with P and has at least two characters.

Query

Output

Name	Education
Garima	PG
Hans	PG

Example 6: Find the employee name, department, and education of the employee who is working in the department that starts with ‘E’ and ends with ‘g’.

Query

Output

Name	Gender	Education
Ajay	M	Doctoral
Babloo	M	UG

Example 7: Find the employee id, name, and department where the month of joining does not contain the word ‘r’.

Query

Output

EmployeeID	Name	Department
1009	Ivanka	Admin

Must Read: SQL Comparison and Arithmetic Operator

Wildcard characters and ILIKE

ILIKE work in a similar fashion as LIKE but it has one additional feature that it is not case-sensitive i.e. if you want to select all the department (in the above employee dataset) that does not contain the word r and R, you can simply use ILIKE instead of LIKE.

Query

Output

EmployeeID	Name	Department
1006	Fredy	Sales
1007	Garima	Sales
1008	Hans	Admin
1009	Ivanka	Admin
1010	Jai	Peon

Conclusion

In this article, we have discussed how to use Like and ILIKE operator in SQL with the help of examples.

Hope this article, will help you in your data science/data analysis journey.