Like Keyword in MS SQL Server


SQL Server Like keyword is use with where condition to extend the filter.

Like keyword searches the result in below patterns.

SQL Server use the following character with Like keyword to search result in result set.
1) '_' (Underscore) - It represents a one character in table data.
2) '%' (Percent) - It represents more than one character in table data.
3) [] (wildcard) - It represent list of single character in table data.
We can use above all operator at same time with each other.


Let consider below list of shopping items and we are going to apply Like keyword in this table.

select result set for like and not like examples in sql server

SQL Server Like keyword examples


1) 'L_ _' - List all product whose first character is L and total length is 3.
2) 'L_D' - List all product whose first character is L and third is D and total length is 3 character.
3) 'M%' - List all product whose first character is M and having n length.
4) '%A%' - List all product having character is A at any place and having n length.
5) '_P%' - List all product whose second character is P and having n length.
6) '[I-S]%' - List all product whose first character between I and S and having n length.
See above example one by one.


Use Underscore ( _ ) operator in SQL LIKE

1) 'L_ _' - List all product whose first character is L and total length is 3.

Example

SELECT * FROM tblShoppingList
WHERE ProductName LIKE 'L__%'

Output

like example in sql server with percent sysmbol (%)


2) 'L_D' - List all product whose first character is L and third is D and total length is 3 character.

Example

SELECT * FROM tblShoppingList
WHERE ProductName LIKE 'L_D%'

Output

like example in sql server with underscore symbol (_)



Use Percent ( % ) operator in SQL LIKE

3) 'M%' - List all product whose first character is M and having n length.

Example

SELECT * FROM tblShoppingList
WHERE ProductName LIKE 'M%'

Output

like operator in sql server


4) '%A%' - List all product having character is A at any place and having n length.

Example

SELECT * FROM tblShoppingList
WHERE ProductName LIKE '%A%'

Output

like operator in sql server example


5) '_P%' - List all product whose second character is P and having n length.

Example

SELECT * FROM tblShoppingList
WHERE ProductName LIKE '_P%'

Output

like operator in sql server example



Use Wildcard ( [-]% ) operator in SQL LIKE

6) '[I-S]%' - List all product whose first character between I and S and having n length.

Example

SELECT * FROM tblShoppingList
WHERE ProductName LIKE '[I-S]%'

Output

like operator in sql server example