SQL LIKE Operator

Use the SQL LIKE operator to search for a pattern in a column rather than an exact match.   This differs from the equals (=) comparison operator which requires an exact match. 

The general setup for using the LIKE operator is: 

SELECT column1, column2, … 
FROM   table1 
WHERE  column1 LIKE matchPattern 

With LIKE specify a pattern to partially match fields.  Here are some wild cards you can use to build match patterns. 

Common SQL Like Operator Wildcards

Note:  There are a couple of more wildcards you can use to specify ranges, but for now, we are going to focus on the most popular ones. 

Here are some example using the common patterns: 

PatternResult
a% Match any value beginning with a, such as aardvark! 
%and Match any value ending with and, such as sand. 
_b% Find any values that have b in the second position. 

SQL LIKE Operator Examples 

Let’s go over some example to give you an appreciate of LIKE simplicity and power.  Consider the WHERE clause: 

WHERE FirstName LIKE ‘R%’ 

You may be wondering what is so special about that search as you could just as easily as written 

WHERE FirstName >= ‘R’ AND FirstName < ‘S’ 

to achieve the same result, but what about finding all names ending in the letter s?  There is no easy way to use the traditional comparison operators to do this, but it’s easily handled with LIKE: 

WHERE FirstName LIKE ‘%s’ 

Here is a query which finds every person whose first name ends in s.

SELECT FirstName , LastName FROM Person.Person WHERE FirstName LIKE '%s';
SELECT FirstName 
 , LastName 
FROM Person.Person 
WHERE FirstName LIKE '%s';

Try modifying the query to find every person whose first name begins with t and ends in s.

/*Write you answer here */
SELECT FirstName 
 , LastName 
FROM Person.Person 
WHERE FirstName LIKE 't%s';

You can also set up patterns to search for text within the middle of a value.  This is useful when you’re working with “dirty” or inconsistent data.  Here we’re looking for every employee classified as a technician:

To do so match using this pattern ‘%technician%’

SELECT NationalIDNumber , JobTitle , BirthDate FROM HumanResources.Employee WHERE jobtitle LIKE '%technician%';
SELECT NationalIDNumber 
 , JobTitle 
 , BirthDate 
FROM HumanResources.Employee 
WHERE jobtitle LIKE '%technician%';

Additional LIKE Resources 

To learn more about the LIKE operator, check out these useful resources: 

>