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.

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

Copy

SELECT * FROM tblShoppingList
WHERE ProductName LIKE 'L__%'

Output

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

Example

Copy

SELECT * FROM tblShoppingList
WHERE ProductName LIKE 'L_D%'

Output

Use Percent ( % ) operator in SQL LIKE

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

Example

Copy

SELECT * FROM tblShoppingList
WHERE ProductName LIKE 'M%'

Output

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

Example

Copy

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

Output

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

Example

Copy

SELECT * FROM tblShoppingList
WHERE ProductName LIKE '_P%'

Output

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

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

Example

Copy

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

Output