Using LIKE, ILIKE, & SQL Functions

[00:00:00]
>> We are gonna talk about functions and we're gonna look at some text search. So the section we're on here is like and functions under the data section. So imagine you're writing something where people can like search for stuff, right? Imagine someone can type a little text string and then it's gonna go search your database to find things like that.

[00:00:27]
If someone says search for pota, you would expect that to show up potatoes, right, I think that's pretty reasonable. So let's read that query, select star. FROM ingredients WHERE title= pota. As you might guess, that doesn't work, right, why? Well, nothing equals potatoes, right? It's actually doing like a full equality check.

[00:01:07]
We need something that's a little bit more fuzzy, right? So the way that we can do that is with something called LIKE. So you can say a word title. Instead of saying equals, you're gonna say LIKE And then you have to say WHERE is it LIKE, right? So if you wanna search for everything where it's just like it ends in pota you would do this.

[00:01:34]
You put the percent here. And then, it would give you anything that ends in pota, obviously that's not anything. So, if we put it here, that means that pota shows up somewhere in the string, right? And there you go. This would also match this, right, because it does actually start with pota, okay?

[00:02:01]
And that is what LIKE does. LIKE basically says like this is going to show up somewhere in here and I'll give you some sort of pattern to match. And again this won't show up anything because you're not giving it any space. Now there's still a problem with this if I do capital P, well like this.

[00:02:23]
This capital is kind of making it mess up because of this, right? It's not matching that P, there's a couple options here of things you can do. We could do something like. So LOWER is a function is gonna take everything in title and make it lowercase, right? Same thing you could do upper and then you just have to make sure that your search string can contain uppercase characters and that would also work as well.

[00:03:01]
That totally works, that's very okay. But there's actually a lot easier way to do that. You can just say ILIKE, which is insensitive like, right? And then you get this it works just fine it doesn't matter. We can make this all sorts of weird things. So that's actually what you're gonna do.

[00:03:27]
I wanted to show you how to use a built-in function because those end up being pretty important. In fact, we're gonna use another one here in just a second. So all right, we're writing our text box that people can search for stuff. If someone searches for fruit, we'll do expect cherry to show up, probably, right?

[00:03:47]
It's very reasonable someone search for fruit and expect cherry to show up. So how do we do that? The type does not exist in the title, so how do we search across multiple different columns. A bunch of ways to do it, but I'm gonna show you one of the most straightforward ways.

[00:04:03]
Let's start FROM ingredients WHERE and then we'll use this CONCAT function. And we're gonna do title and type. Then we're gonna say ILIKE fruit. And there you go. This will search across all these. And because fruit shows up in the type, these are all showing up despite the fact that they don't show up in the title.

[00:04:38]
And CONCAT is exactly what you expect to be it's just taking two strings and shoving them together. Now let me show you why this ends up being a little weird, it literally is just putting strings together, right? So what happens if I search for something like rryfru? Well, you're gonna get all the berries because the rry and the fru get pushed together into matching that stream, right?

[00:05:14]
Now, for the most part, if someone's searching for rryfru, what the hell are they looking for, right, I don't know. You might as well give them something, but that is a little bit strange so you have to be kinda aware that it's a little bit strange. And I did want to show you that you can use multiple these together.

[00:05:39]
Right, that does work and then you can do Now except, yeah we're doing lower, so that's why that doesn't match. But you can do multiple does functions change and there's a bunch of those built in functions to Postgres. Most of the things that you expect coming from like JavaScript and stuff like that exist.

[00:06:11]
I think, Now is one of them we can actually get like the time it is right now there's a bunch of them. That's kind of a fun little trick that you can do with some of these built in functions. You can select LOWER, It'll actually give you the results of some of these.

[00:06:34]
If you want to try them out. You just do select and then whatever you wanna try. Yeah, and so I did leave you a link here in the ducts, there is a ton a ton a ton a ton of functions and operators to do here. We are not even gonna like scratched the surface and how many of them there are.

[00:06:54]
I think we're gonna do coalesce here in a little bit because that one's really useful. But I showed you like now, you can do like regular expressions if you're feeling like you want to go and crazy, like there's a lot of stuff you can do. Exists, that's another one.

[00:07:12]
There's like arrays and such. But not the point, we're not gonna go too much into those today. I just want you to know that they are there. Wanted to show you so far I show the circuits. I showed you the percentages which basically means it matches 0 to many things, right?

[00:07:35]
So, for example, if I put pota, on the front side of this, this is going to match zero characters, right? On the back side of this, this is going to match a lot of characters. So it's zero to many characters. When I'm done, I want to match exactly one character.

[00:07:57]
That's what this underscore does. It matches precisely one character. So if there was a, I don't know churi in here or something like that, it would also match that. But the underscore there will match exactly one character. So again to try that home, if you wanna do like berry, this will match all berries, right?

[00:08:21]
But this would match any of them because there's no aberry or bberry, something like that, right? So, and then again, using those built in functions, you can actually get into full regular expressions if you want to, but we're not gonna get into that because I do not wanna do regular expressions.