Like and Wildcards in SQL

So let's see these put together: LIKE and wildcards. Now, as soon as I open up a file, what's the first thing that I do? Connect, right? N-A-N-A—it doesn't know what database I wanna use.

So I've gotta click on that, choose my server, and make sure it connects to the correct database, the company database, which is the correct one. And now I can get started. I wanna look at my users table.

So I'm gonna select all the columns from the users table. And right now I'm seeing all of the users here. And in here I can see, for cities, that there are lots of different cities.

Some of them have "port" somewhere in them. They could end in "port, " or they could start with "port." I want to find only the rows where the user city—now, if I typed in equals, I'd be looking for a very specific city.

So let's say, for example, I copy and paste—oops, single quotes—Port Almeda. This would find me all the people from this Port Almeda. If I had multiple people, they'd be listed here.

In this case, I only have one person, but there could be many people. Equals is used for an exact match. Now, if I switch to LIKE, I have now opened up the possibility that I could use wildcards.

If I run this query right now, it works the same as an equals. But because I'm using LIKE, I have the opportunity to now use percentage signs or underscores. If I use a percentage sign here, I'm saying anything that starts with "Port, " anything that has any number of characters after—I don't know how many characters; I don't know what they are.

And because I typed in capital P, because I'm using LIKE, it must be a capital P. If I use a lowercase p, see how it doesn't work? Nothing starts with a lowercase p for Port. Of course, if I used ILIKE, then it would work because ILIKE is case insensitive. We don't care about the case.

So normally in PostgreSQL, I use ILIKE because most of the time I don't care about whether it's capital or lowercase—most of the time. Now, what would happen if I put the percentage sign in the beginning instead? This would be anything that ends in "port." I don't know what comes before the "port"; I just know something comes before "port."

What if I put them before and after? Now, I could have characters coming before, or I could have characters coming after, but the percentage could be zero characters, one, or more characters. So in the case of "Port, " this character—that would be zero, but there'd be some here. In the case of "Boilport, " the last one is nothing, but the first part is something.

It's just saying there could be an option that there could be characters. For example, what if you had "New Portsmouth"? This would find "New Portsmouth"; it would find that. But what if you didn't want to include ports in the middle? What if you only wanted ports at the beginning and the end? You could say it starts with a "Port" or it ends with a "port, " because this allows characters to be after, this allows characters to be before, and you're saying that either one is good enough for me.

Include rows where it starts with "Port" or include rows if they end with "port." That would not have anything that's in between, where it's "New Portsmouth, " right? Because it either has to start or it has to end. So there are different ways you can mix and match these things.

I know that goes a little above and beyond what the example here is, but I like to make sure that we understand what's going on here. All right, anything with SQL Server, we skip those. We're just skipping the SQL Server part, ignoring all that SQL Server part.

Now, when we put these two together, looking at our products table, we see these titles here. If I look for rows where the title contains something—and these are strings—that contains "Paper." Now, if I don't use any wildcards, that's like saying equals.

Is anything just called "Paper"? No, nothing is just called "Paper." Does something end in "Paper"? Nope, nothing ends in "Paper." Does something start with "Paper"? Nope, nothing starts with "Paper."

Is "Paper" in the middle? Ah, yes, something has "Paper" somewhere in between, right? Something before or after the "Paper." But I'm maybe not just interested in just paper products. I also want their price to be more expensive, over $30.

I don't wanna see these cheaper paper products. So I'm also gonna require that for a row to be included in my result set, not only does it have to have "Paper, " it also has to have a price that's greater than $30. Now both things must be true.

That's very different from saying either can be true. If I say OR, it will include a row that's either "Paper" or more than $30. So this row—is it more than $30? Yes, it is.

Is it "Paper"? No, but either thing could be true for it to include the row. As long as one of those is true, that's enough. However, if you say AND, they both must be true.

It must be "Paper, " and it's gotta be more than $30. So be careful of that OR versus AND. We skip the SQL Server.

We go to here. And when I'm looking in my orders table, if I'm looking for everybody who is from Florida, I can look at the state and say, I wanna look for people from Florida. And these are all the people from Florida.

Don't need a wildcard or anything for that. Right, just looking for Florida. Or I don't have to look in the same column.

I could also say, hey, let's look in another column. And as long as the zip code is equal to—and this is a string—this zip code, if either one of those is true, include those rows. So for example, these are all from Florida, but then these are the zip code.

They happen to be from Tennessee. The point being that when you do these WHERE clauses and then you do AND/OR, you don't always have to use the same operators. You don't have to look in the same columns.

You can mix and match stuff between different columns. Now, if you're looking for NOT, I like to do the positive before I do the negative. So here, if I'm looking for all of my products—and notice that there are tags.

If I'm looking for WHERE that tags column equals "Emeralds, " these are gonna find all the Emerald products. But if I wanna see everything that's NOT that, meaning I want to exclude those. For example, what if somebody has a latex allergy, and you wanna get rid of all the latex things? So you wanna see everything but latex, right? So here, they don't like Emeralds.

They're allergic to the color Emerald. I don't wanna see the Emeralds. So I wanna see everything that's NOT that.

I could say it's NOT that they're equal to Emerald. Now, you're not gonna see Emerald here as you scroll through because we said we wanna see everything that's NOT that. I think it's always important, personally, to find the thing that you're looking for first to make sure that you found that, rather than saying NOT first.

Why do I say that? Because what happens is you can't see the thing that you're excluding. So what if I said this and I made a typo? Am I gonna see these? No, because there's nothing with that name. So what happens is if I search for that and nothing comes up, what am I excluding from my results? Nothing.

I like to make sure that what I'm trying to find is actually successful—to say, "Oh yeah, that's what I want to exclude." Like, if I wanna exclude latex, I'm gonna find all the latex to make sure I found them all. And then I'm gonna say everything that's NOT that.

I like to see the positive thing, because I know from past experience, if you make one stupid little typo, you can't see that very well if you're saying NOT. Because how much did you exclude? Nothing. It's not easy to see that.

So search for it to make sure you found the thing that you wanna get rid of, and then you can add the NOT to see everything that's NOT that. Now, I did say earlier that you could move the NOT over to say things like NOT LIKE.

If it's a symbol, though, you can't do that. Now, if it's equals—does anybody remember there was an earlier slide how I said there was another way you can do inequality? Do you remember there was an exclamation? You could also do that as well. So you could say it's NOT equal to.

This is a little bit more of like a programmer-y type of way of doing things. Some people don't like it to look so kind of geeky, code-y kind of thing. I'm fine with it because I've done other programming languages, but some people like to use more plain-English-type ways of doing things.

Both will give you the same results. It's kind of just personal preference. But what if you were using things like—for example, what if you were using a wildcard in something? If you were using something LIKE, and maybe you were using "Emer" with something after it, you don't always have that exclamation mark that you can just add to things.

The NOT will always work. And if it were a word here, you can technically move this over here—tags that are NOT LIKE that—but that doesn't work with symbols. So what I can guarantee is that no matter what you're doing after it, NOT will always work, guaranteed.

I don't care what you put after it. That could be BETWEEN, IN, less than, greater than—anything comes, NOT will always work. So this is one area where sometimes you can write it a few different ways, depending on what you're doing.

Personally, if I was doing the equals, I would personally like to write it like this. That is my personal preference, but it is just a personal preference because it's less writing. Some people will like to write it like this, and that would work equally fine.

All right. So this is the file 2.0 LIKE and Wildcards. So let's open that up from your SQL Level One class files.

So 2.0 LIKE and Wildcards. Let's go through this. Go through the warmups and the challenge questions, and in a little bit we'll come back and we'll talk through those challenges.