Like Statement in PostgreSQL | Using LIKE to find Patterns

What's going on everybody? Welcome back to another video. Today, we are continuing our post gray SQL series and in this lesson, we're going to be learning about the like statement. Now, this allows us to search for patterns in our data. It's super powerful. I use it all the time. And so, this is something that I absolutely think you need to know how to use. Up here, I just demonstrate really quickly what we're going to be using. We're going to use the like statement, but within it, there's two characters that we're going to be using. It's a wild card and it's an underscore. Now, this percent sign is the wild card. It equals a single character. I'm not going to go into depth right now on what that means. I'm just going to demonstrate to you. You will understand it very quickly once we get into it. Now, what we're going to do is we're going to use this in a where statement. So, we're going to say where and then we're going to say, let's do character name. So, we're going to say where our character name right here is like and this is where we get to specify the pattern that we are going to be looking for. So, let's put our quotes in here and let's start with an easy one. So, we're going to do an L and then we're going to do a percent sign. What this says is we're looking for a pattern where it starts with the letter L and then the wild card means anything can come after it. That's what that means. We're just searching for starts with an L. Anything can come after it. Let's go ahead and run this. You can see it only returns two rows. We have Luke and we have Leia. Now, that's because their name start with L. But, what if we put a percent sign right here, right before it. So, now we're saying anything can come before it, anything can come after it. We're just looking for the letter L. Let's go ahead and run this. Now, you can see we're going to get the same output and this is to be expected because this statement is case sensitive. If we use a capital L, it's only going to look for specifically a capital L. Let's replace this with a lowercase L. Now, let's go ahead and run this. And now we have other people in our output. We have Padme Amidala and Han Solo. Both of them have an L in the Solo and the Amidala at the end. And actually, Darth Maul as well with the L at the end. We don't have Leia in there anymore because Leia does not have a lowercase L in her name. So, that is something to be aware of. We have to be case sensitive when we are writing this out. There are of course ways around that, but it gets more advanced and we will actually that in a future lesson when we look at string functions where we can make all of these lowercase or all these uppercase and then we can search for it and then we won't don't and then we don't really have to worry about it being upper or lower case, but that's a bit more advanced than what we're looking at today. Now, let's go back and let's just look at all of our data really quick. Before we searched for something that starts with an L and this is really common, but sometimes you want something that ends in something. We can do the opposite here where we use our wild card and we say, okay, we want this to end in something else. Let's do where the character name ends in ER. So, we're saying it ends with ER cuz there's nothing coming after it at the end, but anything can be before it. Let's go ahead and run this. We only have two in our output. It's Skywalker and Vader with the ER at the end. I think we understand the wild card, but let's take a look at the single character now. Because I use the single character especially when I'm looking for much more specific patterns. I'm going to demonstrate this to you and then I will kind of walk through an actual use case, but let's say I'm looking for Yoda, but I can't remember. I know it's a four-letter word. I know it ends in DA, but I can't remember what comes before it. So, I can go like this. I can say I want one, two and then with two characters after it. Let's go ahead and run this. Yoda is going to be the only one that returns because the only one that fits this exact pattern. Now, if we just searched for DA, we didn't know exactly what it's going to look like. We had it like this. We're just searching for DA anywhere in the character name. Now, we're going to get other in our output. Now, we have Padme Amidala and that's not what we're looking for. So, the single character really allows you to be a lot more specific with what you're looking for. Now, for just a second and this is something I do all the time and this is a bit more advanced than maybe you'd be expected to be seeing this, but I use like with things like the date column all the time, transaction dates columns, all these different things. And unfortunately, with birth date and let's come in here. We'll say birth underscore date. We're going to say is like, let's say really quick. Let's say we want the birth date to be like 1977. So, we're going to say 1977 and let's go ahead and try to run this. We're going to get an error. So, we can't actually use the like operator with date columns. It's just not allowed and that's totally fine. But, I want to do this and I do this all the time because it's super useful because I'm looking for dates in in a specific range or I'm looking for dates that start in 1990s, but I don't want to, you know, specify a specific date cuz I'm looking for something else. I still like to use the like statement and this happens a lot. And all we have to do is convert this. And this wasn't actually part of my lesson, but since we're doing it, um I'm going to convert this birth date to text. I'm just going to use this double colon. I'm going to convert it to a text column in the where statement. It doesn't actually in the column change it like in our table. It's not changing anything. Just within this query, we're converting it to text and then we're searching it. Let's go ahead and run this. And there we go. And now, it still is a date column in our output, but we converted it to text so that we could use the like statement on it. I do stuff like this all the time. It's super useful. There's a ton of great use cases for it, but I just wanted to demonstrate that to you cuz I actually do that a lot and I want you to know how to do that cuz I think it's really neat. That's all we're going to look at in today's lesson because honestly, these are super powerful. Just using the like operator is super powerful. There's so many things that you're going to do when you're working with real data when you just have to dig in you're like, okay, I know there's something like this in there, but I don't know the exact value and this helps you figure it out and helps you get there and search for those patterns. I really hope that this was helpful. If you like this lesson, be sure to like and subscribe and I will see you [music] in the next lesson.