Filtering with LIKE and Pattern Matching
In this tutorial, we will introduce 3 operators: , AND which allow you to search the patterns with a combination of wildcards.
LIKE and NOT LIKE
The and operators can be used to find matches or non-matches of a specified pattern. These operators can be accompanied with wildcards and .
The can represent zero or multiple characters, and represents a single character.
Syntax
IN
Another helpful operator is the operator. Instead of using multiple conditions for multiple values, we can simply use the operator to specify multiple values in a single line.
Syntax
LIKE and NOT LIKE with Wildcards
The following table shows examples using and wildcards.
| Example in Query | Definition |
|---|---|
| Finds any values that starts with "a" | |
| Finds any values that ends with "a" | |
| Finds any values that have "ae" in the middle | |
| Finds any values with "b" in the second position | |
| Finds any values that starts with "a" and ends with "o" | |
| Finds any value that starts with "a" and has 3 characters | |
| Finds any values with "a", "b" or "c" | |
| Finds any values with "a" to "f" | |
| Finds any values that starts with "a" and has "l", "m" or "n" | |
| Finds any values that starts with "a" and has "c", "d", or "e" in the middle and ends with 2 characters |
Example 1: WHERE with
Katie, the kindergarten teacher is preparing for Parents' Day and needs your help again to gather the scores data for one of her students, but she's unsure how to spell the name of the student. She's certain that it either starts with "ke" and ends with "y".
Example 2: WHERE with
The sports journalist is doing her research on a United States athlete known for the swimming event. She knows that the athlete's name ends with "Phelps", but couldn't remember the full name.
Example 3: WHERE with
The sports journalist is writing an article about the most celebrated athlete in Athletics and wants you to compile some data on him. A crucial clue is part of the athlete's name starts with "u" and ends with "n".
Practice Questions
Next Lesson
Manipulating with CASE