From the course: MySQL Data Analysis

LIKE wildcard examples - MySQL Tutorial

From the course: MySQL Data Analysis

Start my 1-month free trial Buy for my team

LIKE wildcard examples

“

- [Instructor] In this lecture we're going to go through some examples of when you would use a wildcard with a LIKE operator. This is a really handy chart which walks you through how the different wildcard operators are going to work with a LIKE. I would recommend printing this out and keeping it next to you as you're starting to master the LIKE operator. Once you get really comfortable with it, you won't need it. The stuff really is not that complicated. But as you're first getting up to speed, I think it can be really helpful to have this handy. In this case, we're looking at our film data. We're pulling out title and description, but we only want records where the description is like dentists. So, in this case we have the multi-character wildcard before and after dentist. So, we're going to be looking for any values where the word, dentist, occurs in the description. It could be at the beginning, in the middle, in the end. Having the wildcards before and after the pattern tells SQL to return records anytime that pattern shows up in the value. Let's go over to Workbench and just run through a bunch of examples. I think that's probably the best way for you to grasp this. Alright, here we've got a pretty simple query. We're selecting title and description from film. We'll run that and you'll see all of the titles and descriptions in our database. And if we come up here, we can add a WHERE condition and we're going to use LIKE this time. So, let's say we go with a WHERE description LIKE, and I want to look for everything that the description says "Epic," in it anywhere. So, by putting the multi-character wildcard before and after, this is sort of the most flexible thing that you can do. Make sure to close the single quotation marks there. We were having a little error warning before. When we do this, it's going to look for Epic anywhere in the description. And we see here we've got 42 records. So, let's change that and see what happens if we just look for Epic with just the wildcard after. Here, what we're saying is look for descriptions that start with Epic and then have any number of trailing characters after the word, Epic. But you see there are none. All of the titles start with A, so nothing begins with Epic. Let's similarly do another one where we look for things that end in Epic. Again, we find nothing. So, here we're looking for the pattern Epic and it can have any number of characters coming before it with this multi-character wildcard. Let's, instead of looking for Epic, let's change that to China. So, now what we're going to be doing is trying to find any descriptions that end in China and they can have any number of characters coming before them. We do this and we do find some matches. So, here there are 37 descriptions where that description ends in China. Let's do now a single character here just to show you the difference. So, now we're going to be looking for descriptions that end in China and have one character before that. It might be obvious what's going to happen, but I'm going to run this anyway. And we see that there are no records because previously when we had run the multi-character, which I'll run again, we see that all of these descriptions have a lot of characters before China. So, when we limit to just a single character before China, we're not going to return anything. Let's do another one where we do the same on title and let's actually look for a title that is equal to something. And we're going to start with ALLADIN CALENDAR. So, let's take a look at this. Alright, we see no matches there. I think I misspelled ALLADIN. Let's try it like this, okay. Okay, so we see our one record where title is equal to ALADIN Calendar. I'm going to go ahead and change this to a wildcard to illustrate some things. So, first, I'm not actually going to change this from an equal sign to a LIKE, I'm just going to put that in here so you can see what happens. And in this case, because we didn't put LIKE, SQL's treating this as a literal value, so it's actually looking for a title that contains underscore. But if we put the LIKE operator in here, now SQL knows that this is pattern matching we're trying to do and it should pop that up. And same thing here, if we were to do another character matching here, it will work again. We can also do NOT LIKE to illustrate how we will now strip out anything that looks like this. And you see we've got 999 rows instead of a thousand films that are in the database. So, hopefully this is a good quick overview of how we use these LIKE operators and wildcards. Like I said, this is very powerful stuff. I would just play around with this, try it out on your own and you should be able to get up to speed with this stuff pretty quickly.

Contents