SQL Like Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Or used to search for a specified pattern in a column.

SQL LIKE Syntax

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

 

LIKE Operator Example

“Persons” TABLE:

P_Id	LastName	FirstName	Address	City
1	Suhail	Osmani	Maharaj Nagar	Sitapur
2	Maxwel	Tom	Biswan	Sitapur
3	Rahi	Ayon	Okhla	Delhi

Now we want to select the persons living in a city that starts with "s" from the table above.We use the following SELECT statement:

SELECT * FROM Persons WHERE City LIKE 's%'

The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.
Output:

P_Id	LastName	FirstName	Address	City
1	Suhail	Osmani	Maharaj Nagar	Sitapur
2	Maxwel	Tom	Biswan	Sitapur

Next, we want to select the persons living in a city that ends with an "r" from “Persons” TABLE.
We use the following SELECT statement:

SELECT * FROM Persons WHERE City LIKE '%r'

Output:

P_Id	LastName	FirstName	Address	City
1	Suhail	Osmani	Maharaj Nagar	Sitapur
2	Maxwel	Tom	Biswan	Sitapur

Next, we want to select the persons living in a city that contains the pattern "tav" from “Persons” TABLE.
We use the following SELECT statement:

SELECT * FROM Persons WHERE City LIKE '%elh%'

Output:

P_Id	LastName	FirstName	Address	City
3	Rahi	Ayon	Okhla	Delhi

It is also possible to select the persons living in a city that NOT contains the pattern "elh" from “Persons” TABLE, by using the NOT keyword.
We use the following SELECT statement:

SELECT * FROM Persons WHERE City NOT LIKE '%elh%'

Output:

P_Id	LastName	FirstName	Address	City
1	Suhail	Osmani	Maharaj Nagar	Sitapur
2	Maxwel	Tom	Biswan	Sitapur