Explore SQL Wildcards: Powerful Tools for Flexible Database Searches
Discover how underscores and percentage signs in SQL enable efficient database filtering, allowing matches based on flexible patterns rather than exact characters. Enhance your queries by mastering the distinction between case-sensitive and case-insensitive searches using "LIKE" and "ILIKE."
Key Insights
- Understand how underscores function in SQL as wildcards representing a single character, allowing specific length pattern matching, such as using three underscores "__ _" to match names like "Marty" or "Betty" but not "Dougherty."
- Use the percentage sign (%) wildcard in queries to match any number of characters, including none, enabling flexible searches that can find emails ending with "@gmail.com" or names starting with "B" regardless of character count.
- Distinguish between "LIKE" and "ILIKE" in Postgres SQL: "LIKE" performs case-sensitive searches, differentiating uppercase and lowercase letters, while "ILIKE" provides case-insensitive matching, typically more useful in general queries.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
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.
