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.
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:
|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 examples to give you an appreciation 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';
Try modifying the query to find every person whose first name begins with t and ends in s.
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%';
Additional LIKE Resources
To learn more about the LIKE operator, check out these useful resources:
- How do I use the LIKE clause in SQL Server?
- Use Pattern Matching in SQL Server to Filter Results
- The recommended book SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach