In this video, we’ll walk you through how to use the
LIKE clause. We’ll show you how to 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 ranges. For instance, you may want select all names beginning with “
B” or “
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.
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
Let me change my query here real fast. I can say last name like, and you see where I say,
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.
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's got
Bailey, and as I scroll through, you can see
Baker, and others. It’s definitely matching everything that starts with a ‘
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.
WHERE LastName LIKE '%y'
If I run this, you’ll see I get
Bailey, and as I continue on I’ll get
In fact, there are 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
C. You can see here, we used the brackets and then
A-C. That means the range of characters
WHERE LastName LIKE '[A-C]%'
When I run this, it’s going to be any last name that starts with
C and then matches anything else. If I did for instance,
D, I should get more than 3,500 rows. In fact, I get 4,000 rows.
If I did, I’m curious,
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 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 reading today. Take care.