Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The where clause also supports two operators that are specific to SQL, the like operator is used to match particular values or patterns in a column. Let's take a look at this, we'll use the world database here, so using the like operator, i can do something like this, SELECT Name, Continent. And population from country where name like, and give it a pattern.
We'll order by name. So when I press go, we'll get a list of the 18 rows that match this pattern where the word island is somewhere in the name. So we have all of these names that have the word island or islands because, island is included in islands. So Virgin Islands, we have the Sandwich Islands, Solomon Islands, Norfolk Island. All of those have the word island in them someplace. So you'll notice the percent signs.
One there, and we have one here. The percent sign is a wild card in this context. It matches 0 or more characters in a row. So by putting percent signs before and after the string. I've matched all the strings with island anywhere in them. I could match only those strings that end with island by eliminating the percent sign at the end of the string, like this. And now, I'll only get the ones with the word, island, at the end. So I won't get. Cayman Islands because there's an s there and island is not at the end.
So when I press go, you see there's really only three that match having island at the end of the string. Now alternately, I can put that percent back at the end and I can eliminate the percent at the beginning. And I'll only get those rows where island is at the beginning and you'll notice we have none of those so we got no result what so ever because there aren't any countries that begin with the world island. You can also match a single character by using the underscore like this. So. I can say underscore A percent and that will get anything with an A in the second position.
Any letter in the first position the letter a in the second position and anything after it. So we got all of these countries. There's 57 of them that have an A in the second character. So there's one more operator you should know about for filter results and this is the in operator. The in operator is used to select results that match values in a list like this. So instead of like, we're going to say we're continent in. Europe.
Asia. So I'm giving it a little list. It just has two in the list this time. And this is a list of continents. And when I select Go, I get all of the countries that are either in Europe or Asia like that. So, the IN operator is used to select results that match values in a list. And the list, in this case, is a literal list with parentheses and literal strings in commas. The IN operator is also used for subselects. And subselects.
Are covered in a later lesson in this course. So the liken in operators allow you to refine your searches beyond the normal boolean operators. This adds power and flexibility to the where clause. My SQL allows matching values based on regular expressions as well, and I'll cover that in the next lesson.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104258 Viewers
56 Video lessons · 116197 Viewers
71 Video lessons · 85407 Viewers
131 Video lessons · 40894 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.