Click here to Skip to main content
13,139,569 members (55,031 online)
Click here to Skip to main content
Add your own
alternative version

Stats

4.6K views
5 bookmarked
Posted 23 Oct 2014
MIT

Get Ready to Learn SQL: 5. Query Results Using Pattern Matching

, 23 Oct 2014
Rate this:
Please Sign up or sign in to vote.
Query Results Using Pattern Matching in SQL

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 to 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 clause
  2. understand wild cards

Once you have gone through the lesson, please be sure to do the exercises. These and the examples that follow are based on the sample database. Be sure to get a copy so you can follow along.

Like Clause

The LIKE match condition is used to match values fitting a specified pattern. Unlike the equals (=) comparison operator, which requires an exact match, with the like clause, 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 the LIKE clause.

Where FirstName LIKE '%s'

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

WildcardDescription
%Match zero or more characters
_Match exactly one character

For instance, 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 who 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 New York whose name doesn’t end in S, we would write:

Where FirstName LIKE '%a%' AND EmployeeID >= 3

Matching a 1-800 Phone Number

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

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.

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:

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 '___-__-____'

Answers to Exercises

Congratulations! You just learned how to use the LIKE clause to create 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?

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 24 Oct 2014
Article Copyright 2014 by essentialSQL
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid