Next up, we're going to cover the like.
Operator in MySQL.
It's a pretty useful operator to help us perform basic searching.
So at the moment, we obviously can do things like find it an exact match.
If I'm trying to find a book written by somebody with David as their first name.
I can do select and then let's do author.
Let's do title.
And then author F name, author ll name from books.
And then we can do something like where author first name exactly equals David.
And that's great.
That works.
And we get the two books written by David, but that has to be an exact match, right?
It doesn't work if it's Dave or Dave or something like that.
But a lot of the time we may want to perform some fuzzier searching where it's not based only on an
exact match, and that's where like comes in.
Imagine we're looking for a book.
We can't remember the exact title.
We'll know it when we see it.
But I know the author's first name is David or Dan or Dave or something with a D and then an A.
Well, what we can do is use the like operator followed by a piece of text, a string in quotes that
has a little bit of special magic to it.
Those special characters, those percent signs mean something.
They're called wild cards.
And in this situation, this percent sign and this percent sign mean any number of characters, zero
or more characters, zero or more characters.
So in other words, we're saying, I want to find something where the author's first name follows this
pattern.
There is zero or more characters and then a D in an A, and then zero or more characters.
So let me show this as an example.
Let's do the same thing.
We'll select title first name, last name from books where and then we'll say author first name like,
and then I'll do this on the next line.
I guess just because I want this to be easy to read where author first name is like this pattern I'm
going to lay out.
And if I simply say D A, it's not going to work.
I don't have any matches that are exactly DA But if I instead put a percent sign first and then D A
and then D rather then percent sign again, Take a look.
I found a match here.
Da da da da da da.
And one more day.
So again, the percent sign means optionally zero more zero or more characters.
So it just is saying if we have this pattern percent da.
Percent that it could be at the beginning of the string.
There's nothing here in the case of Dave and David and Dan, or it could be in the middle, which we
don't have right now, or it could even be at the end of the string because this is optional as well.
So let's take a look at another example.
Let's say I want to find any of these collections or any of these books that have a colon in their title.
So I'll just do a select star from books and then I can do where title like.
And then after that, like I can not just write a colon because of course that will only match the exact
match of a colon.
But I'll say some text maybe, and then a colon and then some text possibly afterwards.
In other words, this is saying I just want to see if there's a colon anywhere in the string, the beginning,
the middle or the end.
And it's ugly to look at.
But here we are, hologram for the king Colon.
What we talk about when we talk about Love Colon, where I'm calling from, colon Oblivion.
Colon.
So that is the first of the wild, wild card characters.
You'll see the percent sign.
There's one more I'm going to show you, which is the underscore.
And an underscore means something different than the present sign.
It means exactly one character.
So I could do something like I want to find.
It's hard to come up with a good example here.
Let's look at our books again.
I want to select the books that let's say I want to find all authors who have a first name that is exactly,
I don't know, exactly three or four characters.
Why?
I don't know.
But let's say I want to do that.
I'll do a select start from books where and then author f name like, and then my string is going to
be one character, two, three, four.
It's exactly four underscores.
Remember, those underscores don't mean underscore.
They mean one character.
It must be one character.
And we find Neil, Neil, Dave, Dave, Dave, Neil and John, all of those four character authors.
So if I had instead added a fifth underscore, now we get all the books written by authors that have
five characters in their name.
Patty, David, David.
If I instead did percent signs, remember what this means.
That percent sign means zero or more characters.
So this could match zero characters.
It could match 1000 characters, although our var strings don't support something that long.
The way I have it set up.
So we get every single author, every row in our data set.
As long as there is an author f name, I think one of them has null.
So we're not getting that one.
So that's really important to understand the difference percent sign you use generally if you want to
find some match within a string, anywhere within a string, you're looking for some smaller portion.
You use the underscores if you know with precise detail that you're looking for something of a certain
number of characters, or if I don't know, this might be really bizarre, but let's say I want to find
this pattern.
I want to find authors who have a first name where it is a letter and a character, and then exactly
one more character, and we find Dan.
But if I had instead just had a percent sign on either side, this says, Find any authors first name
that has an A anywhere in it.
And a lot of them do, right?
Jhumpa the A is at the end.
Dave It's in the middle.
Michael It's kind of in the middle towards the end.
Another thing we could do if I wanted to find all of the author's first names that let's say, end with
a an N, they end of the letter.
N to say that it must end with that letter, I could do select star from books where author I think
I said first name and then like and my pattern is going to say anything and then mandatory n at the
end with nothing afterwards.
And we get Don, John and Dan and that's pretty much it for this video.
Like is powerful.
It's not fully powerful.
Like if we if you know, regular expressions or something, but it still offers a lot of nice searching
features thanks to those wild card characters.
Remember, a percent sign means anything zero or more characters.
And then a single underscore means exactly one character.