WHERE: Filtering Data with Strings

In our last module, we got all of the tags from the dbo.Tags table, but usually when we’re working with data, we only want rows that match a filter. For example, if I’m shopping for cars, I only want to see cars with a price that I can actually afford. (Well, that’s not true, I wanna see the cars I can’t afford, but that’s another story.)

StackOverflow Example of WHERE

Take another look at the Tags page at https://StackOverflow.com/tags:

See the “Type to find tags:” box at the top? Type SQL in there, hit enter, and see what happens.

Now we’re seeing a list of tags – but only the ones with the word SQL anywhere in the name.

In our last lesson, we selected all of the data from the tags table. As a reminder, here’s that result set:

See the TagName column? That’s the one we need to filter by, and that’s where the WHERE comes in. When we’re filtering for strings, we have to frame them in single-quotes like this:

Run this query at Data.StackExchange.com, and you get:

We only got one tag back, not like the StackOverflow.com search, because when we use the equals sign in SQL Server, we’re saying we want exactly the tag named ‘sql’ and nothing else.

But how do we mimic what StackOverflow.com is doing on the tags page when you type in SQL, and it returns all tags with the letters SQL anywhere in the tag?

We’re going to need the LIKE operator:

Run this query, and you get:

There’s a couple of different concepts at play here.

The % sign is a wildcard to be used with LIKE. Our query said ‘%sql%’, so that means we’re looking for sql anywhere in the string. In the above screenshot, some of the TagNames end with sql, some start with sql, and some just have sql somewhere in the middle. You can narrow down your results by using that % wildcard in different places:

• LIKE ‘sql%’ – only finds strings that start with the letters sql.
• LIKE ‘%sql’ – only finds strings that end with sql.
• LIKE ‘%sql%’ – finds strings that have sql anywhere in them.

Like doesn’t mean a kinda-sorta-search. With a name like LIKE, you would think it would find you similar stuff. For example, if I said LIKE ‘sql’, it might also return Oracle or DB2, because those are also databases like SQL Server – but that’s not how it works. It’s just pure string searching.

Again, the rows aren’t necessarily in order. SQL Server doesn’t find the tags that start with SQL first, then the ones with SQL at the end. They’re just returned in an unpredictable order. We’ll talk about sorting our data later.

Now, you try.

Click Users at the top of StackOverflow, and check out the list of users:

Write a query to show only the users whose name starts with Brent.

When you’re done with that, move on to the next lesson.