Get Ready to Learn SQL Server: 5. Filter Results Using Patterns

Introduction

In this lesson, you are going to explore how to do pattern matching with the SQL Where clause LIKE operator. Using this phrase allows us perform partial matches of data values and obtain answers to questions which can’t be done with conventional comparisons.

The lesson’s objectives are to:

1. Learn about the LIKE match condition
2. Understand wild cards

Like Match Condition

The LIKE match condition is used to match values fitting a specified pattern. Unlike the equals (=) comparison operator, which requires an exact match, with LIKE we can specify a pattern to partially match fields. An example where clause using the LIKE condition to find all Employees whose first names start with “R” is:

WHERE FirstName LIKE 'R%'

You may be wondering what is so special about that search as you could just as easily have 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'

The ‘R%’ and ‘s%’ are patterns. Patterns are created using placeholder characters. There are several special characters used:

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.

Let's look at the % wildcard. The pattern ‘%and%’ would match word ‘Wand’, ‘and’, or ‘Standard.’ To find all state abbreviations starting with the letter N, we could use the pattern ‘N%’ as this would match all values whose first character is “N” and then any characters afterwards.

Yet, since state abbreviations are two characters ‘N_’ is more accurate, as this states to first match ‘N’ and then one and only one character thereafter. We can also match an anti-pattern using NOT. If you’re looking for all names that do not end in S, the clause to use is:

WHERE FirstName NOT LIKE '%s'

This would match ‘Baker’, ‘Michigan’, or ‘Wolverine,’ but not ‘Sales’ or ‘Kites’.

As with other clauses, LIKE comparisons can be combined with other comparisons using AND and OR.

So, to find all employees in the AdventureWorks2012 database who are managers and females, we can use the following query:

SELECT NationalIDNumber,
       JobTitle,
       BirthDate,
       MaritalStatus,
       Gender,
       HireDate
FROM   HumanResources.Employee
WHERE  Employee.JobTitle LIKE '%manager%'
       AND Employee.Gender = 'F'

Matching a 1-800 Phone Number

So if you wanted to search for a “1-800” phone numbers, you could do a search like:

WHERE PhoneNumber LIKE '%800%'

But that could match more than you bargained for as numbers such as 1-248-703-9800 could also match. So, you could refine the search to be more specific.

WHERE PhoneNumber LIKE '%(800)%'
      OR PhoneNumber LIKE '%800 %'

To match numbers such as (800) 555-1212 or 1-800 555-1212; however, this could backfire, as now numbers such as 1-800-555-1212 wouldn’t match, of course you could catch this with additional match terms. The final result would be:

WHERE PhoneNumber LIKE '%(800)%'
      OR PhoneNumber LIKE '%800 %'
      OR PhoneNumber LIKE '%800-%'

Matching a Social Security Number

You may have a situation where you wish to retrieve all government IDs matching the pattern of a US social security ID. In order to do this match, you could use the following:

WHERE GovernmentID LIKE '___-__-____'

This would match numbers such as ‘123-12-1234', but not ’12-12-12'. If you were looking to find all IDs that didn’t match a social security number, you could write your query as:

WHERE GovernmentID NOT LIKE '___-__-____'

Congratulations! You just learned how to use multiple conditions to create more sophisticated filtering conditions. More tutorials are to follow! Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you. What other topics would you like to know more about?