Click here to Skip to main content
13,591,190 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

2.2K views
Posted 30 Dec 2017
Licenced MIT

How Do I Use the Like Clause in SQL Server?

, 30 Dec 2017
Rate this:
Please Sign up or sign in to vote.
In this video, we'll walk you through how to use the LIKE clause.

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 “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.

Like Clause

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 ‘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.

Like Clause

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 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 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.

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 | Cookies | Terms of Use | Mobile
Web03-2016 | 2.8.180618.1 | Last Updated 30 Dec 2017
Article Copyright 2017 by essentialSQL
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid