SQL LIKE & Wildcards 🔍
Mentor's Note: Sometimes you don't know the full name or code you are looking for. You might only remember it "starts with A" or "contains the word tech". The
LIKEoperator is the "Search Engine" of SQL. 💡
🌟 The Scenario: The Lost Contact 📱
Imagine you are searching for a friend in your phone book.
- The Hint: You remember their name starts with "Sha".
- The Pattern:
Sha% - The Result: You find "Sharma", "Shanti", and "Shakti". ✅
🎨 Visual Logic: The Wildcard Tools
| Wildcard | Meaning | Analogy |
|---|---|---|
| % (Percent) | Any number of characters (0 to many) | The "Infinite Bridge" 🌉 |
| _ (Underscore) | Exactly one character | The "Single Step" 👣 |
💻 1. Using the % Wildcard
A. "Starts With" Pattern
-- Scenario: Find all names starting with 'V'
SELECT * FROM students
WHERE name LIKE 'V%'; -- Finds: Vishnu, Vikram, Vijay
B. "Ends With" Pattern
-- Scenario: Find emails ending in '.com'
SELECT * FROM students
WHERE email LIKE '%.com';
C. "Contains" Pattern
-- Scenario: Find any name that has 'sh' anywhere
SELECT * FROM students
WHERE name LIKE '%sh%'; -- Finds: Vishnu, Rahul, Akash
💻 2. Using the _ Wildcard
The underscore matches exactly one character. Use it when the length is fixed.
-- Scenario: Find 4-letter names starting with 'A'
SELECT * FROM students
WHERE name LIKE 'A___'; -- (A followed by 3 underscores)
-- Finds: Amit, Ajay | Skips: Arjun (5 letters)
💻 3. Case Sensitivity & Dialects 🛠️
Searching for 'apple' vs 'Apple' behaves differently depending on your database.
- MySQL: Usually case-insensitive.
- Oracle: Strictly Case-Sensitive (Must match exact casing).
- PostgreSQL: Case-sensitive by default, but you can use
ILIKEfor case-insensitive searching!
-- PostgreSQL Specific: Case-Insensitive Search
SELECT * FROM students WHERE name ILIKE 'vishnu';
💡 Pro Tip: Performance Warning
"Avoid starting a pattern with a wildcard like
%name. This forces the database to scan every single row because it can't use an index. Patterns likename%are much faster!"