Lesson 17: Pattern Matching with LIKE
The LIKE operator is used in the WHERE clause to search for specific patterns in text columns. This is essential for features like search bars.
Wildcard Characters
SQL uses two main wildcard characters for pattern matching:
- % (Percent Sign): Represents zero, one, or multiple characters.
- _ (Underscore): Represents a single, specific character.
| Pattern | Meaning | Example Match |
|---|---|---|
'A%' | Starts with 'A' | Alice, Apple, Account |
'%z' | Ends with 'z' | Jazz, Buzz, Wiz |
'%test%' | Contains 'test' anywhere | UnitTest, TestData, Retest |
'J_hn' | Starts with J, ends with hn, exactly one character in between | John, Jahn, Jrhn |
Example 1: Finding Names Starting with 'S'
sql SELECT CustomerName FROM Customers WHERE CustomerName LIKE 'S%';
Example 2: Finding Emails from Specific Domain
sql SELECT Email FROM Users WHERE Email LIKE '%@gmail.com';
Case Sensitivity Note
Be aware that LIKE behavior regarding case sensitivity (e.g., matching 'A' vs 'a') depends entirely on the specific database system (e.g., MySQL is often case-insensitive by default, while PostgreSQL can be case-sensitive).