Oracle LIKE & Wildcards 🔍¶
Prerequisites: WHERE Clause
Mentor's Note: What if you only remember that a student's name starts with "Vi"? You can't use
=, but you can useLIKE. It's the search engine of the database world. 💡
🌟 The Scenario: The Smudged Record 📄¶
Imagine an old file where the end of the name is unreadable. You only see "Sma...".
- Action: You search for LIKE 'Sma%'.
- Result: You find "Smart", "Small", and "Smathers".
💻 1. The Wildcard Tools¶
| Wildcard | Meaning | Analogy |
|---|---|---|
| % | Zero or more characters | The "Infinite Bridge" 🌉 |
| _ | Exactly one character | The "Single Footstep" 👣 |
Examples¶
-- Starts with 'A'
SELECT name FROM students WHERE name LIKE 'A%';
-- Has 'sh' anywhere
SELECT name FROM students WHERE name LIKE '%sh%';
-- 4 letter name starting with 'R'
SELECT name FROM students WHERE name LIKE 'R___';
🏗️ Architect's Note: The Index Trap 🛡️¶
In Oracle, if you start your pattern with a wildcard (e.g., LIKE '%son'), the database cannot use an index. It must read the whole table.
- Architect's Advice: Always try to put text before the wildcard if performance matters! LIKE 'Son%' is much faster than LIKE '%Son%'.