SQL Server LIKE Operator and WildCards

LIKE :The LIKE operator is used to search for a specified pattern in a column. Pattern can include regular characters or wildcard characters. Below are the possible wildcard characters to make pattern.

Wildcard character	Description	Example
%	Compares 0 or more characters in a string.	WHERE ProductName LIKE '%chai%' displays all products where productname includes word 'chai'.
_ (underscore)	Compares any single character in a string.	WHERE ProductName LIKE '_hai' finds all four-letter first names that end with ‘hai’.
[ ]	Compares any single character within the specified range or set of characters like range [a-c] or set [abc].	WHERE ProductName LIKE '[a-c]hai' displays product name ending with hai and first character between a and c.
[^]	Compares any single character not within the specified range [^a-c] or set [^abc].	WHERE ProductName LIKE '[^a-c]%' displays all product name not starting with character range a,b and c.

Examples of LIKE Operator:

Example 1 : Using like operator in where clause

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'paul henriot'

Output:

ContactName        CompanyName

Paul Henriot          Vins et alcools Chevalier

Above query compares table rows with pattern ‘paul henriot’ and returns rows having same value in contactname column.

Example 2 : Using % wildcard character in like operator

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'paul%'

Output:

ContactName        CompanyName

Paula Wilson         Rattlesnake Canyon Grocery

Paul Henriot          Vins et alcools Chevalier

Paula Parente        Wellington Importadora

LIKE ‘paul%’  pattern returns all contactname having clumn value paul followed by zero or more characters. You can also use NOT operator to find rows that doe not match with pattern. For example  NOT LIKE ‘paul%’.

Example 3 : Using [] square brackets wildcard charaters in like operator

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'pa[lut]%'

Output:

ContactName          CompanyName

Patricio Simpson     Cactus Comidas para llevar

Patricia McKenna    Hungry Owl All-Night Grocers

Paula Wilson            Rattlesnake Canyon Grocery

Palle Ibsen                Vaffeljernet

Paul Henriot             Vins et alcools Chevalier

Paula Parente           Wellington Importadora

As above example shows how can you use wildcard character as literal in pattern matching.  Like ‘pa[lut]%’ searches for rows having column value as first 2 characters are ‘pa’ and third can be any of  characters ‘l’,’u’,’t’ follwed by zero or more characters.

Example 4 : Using wildcard charaters as literals in like operator

SELECT ContactName, CompanyName FROM Customers

WHERE ContactName LIKE 'pa[%]'

Above example serches for rows having column value ‘pa%’, here % sign is taken as charater to search from rows of table.

Query's and Interview Questions :

1.  Write a query to get all the details  from employee table whose firstname start with letter 'a'.

Ans : SELECT * FROM EMPLOYEE where FirstName like 'a%'.

2.  Write a query to get all the details  from employee table whose firstname contains letter 'K'.

Ans: SELECT * FROM EMPLOYEE where FirstName like '%k%'.

3. Write a query to get all the details  from employee table whose firstname ends with letter 'h'.

Ans: SELECT * FROM EMPLOYEE where FirstName like '%h'.

4. Write a query to get all the details  from employee table whose firstname start with any single character between 'a-p'.

Ans: SELECT * FROM EMPLOYEE where FirstName like '[a-p]%'.