Skip to main content
Dan Rodney/5 min read

Pattern Matching in SQL

LIKE Pattern Operators

% Wildcard

Matches zero or more characters. 'Smith%' finds Smith, Smithson, Smithers.

_ Wildcard

Matches exactly one character. 'J_n' finds Jan, Jen, Jon.

ESCAPE Clause

Use ESCAPE '\' to match literal % or _ in the data.

ILIKE (Postgres)

Case-insensitive variant — doesn't exist in MySQL or SQL Server.

Master SQL at Noble Desktop

Noble Desktop's SQL Bootcamp covers queries, joins, aggregations, and the full SQL toolkit data analysts use every day.

This lesson is a preview from our SQL Course Online (includes software) and SQL Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.

Next topic. When we're doing these filters, sometimes we're looking for exact matches.

Next topic. When we're doing these filters, sometimes we're looking for exact matches. But sometimes we're not.

Sometimes we're looking for something that has a certain pattern. It's a wildcard. It's not an exact match.

So there are two wildcards that we have. We have underscores and percentage signs. An underscore says a literal fill-in-the-blank.

We don't know what character or number should be there. We just know something should be there. Each underscore represents one single character or number.

If you list three underscores, you would be saying there are three characters or numbers in that place, but I don't know what they should be. So fill in the blank, and you can allow for some flexibility. Percentage sign, on the other hand, says there might be one or more characters.

I don't even know how many characters there should be. And also, it's possible there could be none at all. So a percentage sign as a wildcard says there could be zero characters, there could be one character, or there could be more.

There could be any number of characters, if there are even characters. I don't know. It's very open-ended.

So let's see some examples of how this could work. For example, if you're looking for something where it says at gmail.com, do you know how many characters come before that? No, because you could have any number of characters. I don't know what comes before the at gmail.com. I just know that some characters come before the at gmail.com. I do not know what the characters are, and I don't know how many characters there are.

So percentage sign would work great in a situation like that. If I want to know, let's find people whose name starts with a B. Do I know how many characters come after the B? No, because you could have Barbara, you could have Ben, could have all sorts of different things. I don't know how many characters come after the B. I just know that something comes after the B, but I don't know what they are, and I don't know how many I have.

So, again, percentage sign could work great in a situation like that. We don't always know exactly what we're looking for. So, for example, if I put three underscores, notice I'm using this in a WHERE.

So I'm looking at names, and I'm using LIKE. I'm not using equals. Equals would mean literally it must equal a person with underscore, underscore, T-Y.

Like literally their name would have underscores. It would have to exactly equal that. But because I'm using LIKE, LIKE is a wildcard and says you can use these things which don't represent a literal underscore.

They represent a fill-in-the-blank. So notice that Marty has M-A-R. That's three letters.

That's a match. Betty has B-E-T. That's a match.

But Dougherty, sorry, there's five characters. Sorry, Shannon, you are not a match for this. Now, PostgreSQL has two flavors of LIKE.

They have LIKE and they have ILIKE. I means case insensitive. If you're sensitive to something, it means you care.

You're like I'm sensitive. I care about this. So do you care about case? Like there's a difference between upper and lowercase.

If you care, you use LIKE. If you don't care, you use ILIKE. And typically, most of the time, I don't really care about case sensitivity.

It doesn't matter whether it's uppercase or lowercase. So normally, ILIKE—I like said as kind of a pun. So most of the time, I will use ILIKE instead.

So as an example here, if I say LIKE, I am being case sensitive. Because I used a capital B with any number of characters after, because it's a capital B, it only gets a hit on Brian. It does not give me Bob because it is being case sensitive.

Lowercase B is different from capital B. However, if I switch to ILIKE, then it doesn't matter. It's insensitive to case. So it doesn't matter whether I'm a capital B or lowercase B when I use ILIKE.

So most of the time when I'm doing wildcards, I normally don't care about case. I want to be insensitive to case. So I will say most of the time I use ILIKE to be insensitive to case.

It's only in those rare cases when I do actually care about case that I'll use LIKE. So in the case of a percentage sign, you're saying I don't know how many characters there are. So it doesn't matter whether there are two or four.

Percentage sign says take the place of any number of characters. And there might even be no characters. Like what if somebody's name was B? This would still get a match.

Or any number of things that start with a B, I would get a match.