00:00What's going on, everybody? Welcome back to another video. Today, we are continuing our
00:03PostgreSQL series. In this lesson, we're going to be learning about the like statement. Now,
00:09this allows us to search for patterns in our data. It's super powerful. I use it all the time.
00:14And so this is something that I absolutely think you need to know how to use. Up here,
00:18I just demonstrate really quickly what we're going to be using. We're going to use the like
00:23statement, but within it, there's two characters that we're going to be using. It's a wildcard
00:28and it's an underscore. Now, this percent sign is the wildcard. The underscore equals
00:32a single character. I'm not going to go into depth right now on what that means.
00:36I'm just going to demonstrate it to you.
00:40You will understand it very quickly once we get into it.
00:43Now, what we're going to do is we're going to use this in a where statement. It's going to say
00:48where, and then we're going to say, let's do character name.
00:52So we're going to say where our character name right here is like,
00:56and this is where we get to specify the pattern that we are going to be looking for.
01:01So let's put our quotes in here and let's start with an easy one. So we're going to do an
01:05L
01:05and then we're going to do a percent sign. What this says is we're looking for a pattern where it
01:11starts with the letter L and then the wildcard means anything can come after it. That's what
01:17that means. We're just searching for starts with an L, anything can come after it. Let's go ahead and run
01:23this. You can see it only returns two rows. We have Luke and we have Leah. Now that's because
01:29their name start with L, but what if we put a percent sign right here, right before it? So now
01:37we're saying anything can come before it. Anything can come after it. We're just looking for the letter
01:43L. Let's go ahead and run this. Now you can see we're going to get the same output and this
01:49is to be
01:49expected because this statement is case sensitive. If we use a capital L, it's only going to look for
01:57specifically a capital L. Let's replace this with a lowercase L. Now let's go ahead and run this.
02:04And now we have other people in our output. We have Padme Amidala and Han Solo. Both of them have
02:09an L
02:10in the Solo and the Amidala at the end. And actually Darth Maul as well with the L at the
02:15end. We don't have
02:16Leah in there anymore because Leah does not have a lowercase L in her name. So that is something to
02:22be aware of. We have to be case sensitive when we are writing this out. There are of course ways
02:27around that, but it gets more advanced and we will actually cover that in a future lesson when we look
02:32at string functions where we can make all of these lowercase or all these uppercase and then we can
02:38search for it. And then we don't really have to worry about it being upper or lowercase,
02:43but that's a bit more advanced than what we're looking at today. Now let's go back and let's
02:48just look at all of our data really quick. Before we searched for something that starts with an L,
02:53and this is really common, but sometimes you want something that ends in something.
02:57We can do the opposite here where we use our wildcard and we say, okay, we want this to end
03:02in something else. Let's do where the character name ends in ER. So we're saying it ends with ER
03:08because there's nothing coming after it at the end, but anything can be before it. Let's go ahead
03:13and run this. We only have two in our output. It's Skywalker and Vader with the ER at the end.
03:20I think we understand the wildcard, but let's take a look at the single character now because I use a
03:26single character, especially when I'm looking for much more specific patterns. I'm going to
03:30demonstrate this to you and then we'll kind of walk through an actual use case, but let's say I'm
03:34looking for Yoda, but I can't remember. I know it's a four letter word. I know it ends in DA,
03:40but I can't remember what comes before it. So I can go like this. I can say I want one,
03:45two,
03:46and then with two characters after it. Let's go ahead and run this. Yoda is going to be the only
03:51one that returns because the only one that fits this exact pattern. Now, if we just search for DA,
03:57we didn't know exactly what it's going to look like. We had it like this. We're just searching for DA
04:01anywhere in the character name. Now we're going to get other in our output. Now we have Padme Amidala
04:07and that's not what we're looking for. So the single character really allows you to be a lot
04:11more specific with what you're looking for. Now for just a second, and this is something I do all the
04:15time and this is a bit more advanced than maybe you'd be expected to be seeing this, but I use
04:21like
04:21with things like the date column all the time, transaction dates, columns, all these different
04:26things. And unfortunately with birth date and let's come in here, we'll say birth underscore
04:31date. We're going to say is like, let's say really quick. Let's say we want the birth date to be
04:38like
04:391977. So we're going to say 1977 and let's go ahead and try to run this. We're going to get
04:46an error.
04:46So we can't actually use the like operator with date columns. It's just not allowed and that's totally
04:53fine. But I want to do this and I do this all the time because it's super useful because I'm
04:59looking for dates within a specific range or I'm looking for dates that start in 1990s, but I don't
05:04want to specify a specific date because I'm looking for something else. I still like to use the like
05:09statement and this happens a lot. And all we have to do is convert this. And this wasn't actually part
05:14of my lesson, but since we're doing it, I'm going to convert this birth date to text. I'm just going
05:20to
05:20use this double colon. I'm going to convert it to a text column in the where statement. It doesn't
05:24actually in the column change it like in our table. It's not changing anything. Just within this query,
05:30we're converting it to text and then we're searching it. Let's go ahead and run this.
05:35And there we go. And now it still is a date column in our output, but we converted it to
05:42text so that we
05:43could use the like statement on it. I do stuff like this all the time. It's super useful. There's a
05:48ton
05:48of great use cases for it, but I just wanted to demonstrate that to you because I actually do
05:52that a lot and I want you to know how to do that because I think it's really neat. That's
05:56all we're
05:56going to look at in today's lesson because honestly, these are super powerful. Just using the like
06:02operator is super powerful. There are so many things that you're going to do when you're working
06:06with real data. When you just have to dig in, you're like, okay, I know there's something like
06:10this in there, but I don't know the exact value. And this helps you figure it out and helps you
06:15get
06:15there and search for those patterns. I really hope that this was helpful. If you liked this
06:20lesson, be sure to like, and subscribe and I will see you in the next lesson.
Comments