SQL Text Filtering Explained: WHERE Clause and Wildcards

We're making excellent progress. We will now switch our focus away from filtering numbers to filtering textual data. As we've briefly seen, we can use the where clause to filter text data. However, so far we've only been able to filter by specifying the exact text we're interested in. We'll often want to search for a pattern rather than a specific text string. In the real world, we'll be introducing three more SQL keywords into our vocabulary to help us achieve this. Like, not like and in in SQL, we can use the like operator with a where clause to search for a pattern in a field. We use a wildcard as a placeholder for some other values. To accomplish this, there are two wildcards with like the percent and the underscore. The percent wildcard will match 0, 1, or many characters in the text. For example, the query on the left matches people like Adele, Adelaide, and Aidan. The wildcard will match a single character. For example, the query on the right matches only three letter names like eve. We'd also see names like EVA if it were in our dataset. Eva Mendez, however, would not be visible unless the search criteria looked like this. We can also use the not like operator to find records that don't match the specified pattern. In this query, we are finding records for people who do not have A as part of their first name. It's important to note that this operation is case sensitive, so we must be mindful of what we are querying. We've reviewed one example of where to position each wildcard, but we can actually put them anywhere and combine them. We can find values that start, end, or contain characters in any position, as well as find records of a certain for example, this code on the left will find all people whose name ends in R. The code on the right will find records where the third character is T. What if we want to filter based on many conditions or a range of numbers? We could change several ors to the where clause based on what we know, but that can get messy. We can see an example here where we select the film titles released in 1920, 1930, or 1940. A helpful operator here is in. The in operator allows us to specify multiple values in a where clause, making it easier and quicker to set numerous OR conditions. Neat, right? So the example shown on the previous slide would simply become where Release year in 1920, 1930, 1940 where the years are enclosed in parentheses. Here is another example using a text field where we want to find the title where the associated country is either Germany or or friends.