In this video we’ll walk you though how to use the LIKE clause. We’ll show you how to you can perform partial matches, such as being able to match all names beginning with “B” or ending in “Y.” We’ll also show you how to use use ranges. For instance you may want select all names beginning with “A” “B” or “C”.
Once you have watched the video check out the sample code below. I’ve also included a transcript for you to use.
Transcript about the LIKE Clause
Hey, this is Kris with another SQL Minute. In this episode I want to talk about the LIKE Operator and how you can use it to partially match values on a column. You probably already know how to write a filter for your query and select rows that match a criteria. In this example here, we are matching rows where the last name is Bailey from the AdventureWorks person table.
SELECT Title, FirstName, LastName FROM Person.Person WHERE LastName = 'Bailey'
When I run this, you’ll see that we get 95 rows. There’s also a way where I can come in and do partial matches. For instance, let’s say I wanted to find every person whose last name starts with the letters ‘Ba.’ What I can do is I can use what’s called the LIKE clause.
Let me change my query here real fast. I can say last name like, and you see where I say, B, A, so it’s like Bailey. Then I’m using here it’s a percent key (%), this is the symbol that’s above the five on a US keyboard.
SELECT Title, FirstName, LastName FROM Person.Person WHERE LastName LIKE 'Ba%'
When I run this, it’s going to run through and it’s going to match every last name, and it’s going to match them where the first two characters are ‘BA’, and then any characters thereafter. The percent sign means, match anything past that so it’s kind of like a pattern match.
Let’s see what happens when I run this.
As you can see, now I’m getting 284 rows. It got Bailey, and as I scroll through you can see Baker, and others. It’s definitely matching everything that starts with a ‘Ba.’
Now likewise, I could run this query and find names that end in a certain letter. Let’s do, starts with anything and must end in a ‘Y’. There we have ‘%Y’. Match any characters you want, but the last character must be ‘Y’ in the last name.
SELECT Title, FirstName, LastName FROM Person.Person WHERE LastName LIKE '%y'
If I run this, you’ll see I get Bailey, and as I continue on I’ll get Gray, and Kelley.
In fact, there’s 640 rows. That’s a great way to do a wildcard to match the end. Now another one I want to show you is where you can match people that are in a range. I’m going to bring in a whole new query here and we’re going to get all the people whose last name starts with A, B, or C. You can see here, we used the brackets and then A-C. That means the range of characters A through C.
SELECT Title, FirstName, LastName FROM Person.Person WHERE LastName LIKE '[A-C]%'
When I run this, it’s going to be any last name that starts with A, B, or C and then matches anything else. If I did for instance, A through D, I should get more than 3,500 rows. In fact, I get 4,000 rows.
If I did, I’m curious, A through Z, I would expect to get everything. Here I get 19,972 rows and that should be the same thing as if I comment out the last name filter altogether and run it and I get the same, 19,972 rows.
Hopefully that will get you started in thinking about using patterns. There’s other clauses to use, but we’re running out of time and I at least wanted to introduce you to the like clause so it doesn’t seem so scary anymore. Thank you very much for listening today. Take care.
