Be able to perform searches using wildcards
📚 18 Databases – Wildcard Searches
🔍 What are Wildcards?
Wildcards are special symbols that let you search for patterns in data, just like a detective uses a magnifying glass to find clues. In SQL, the two most common wildcards are % (any number of characters) and _ (exactly one character). They are used with the LIKE operator.
✏️ Wildcard Characters
- % – matches zero or more characters. Think of it as a “wild card” that can be anything.
- _ – matches exactly one character. It’s like a single placeholder.
📊 Example Queries
| Query | Result |
|---|---|
| SELECT * FROM students WHERE name LIKE 'A%'; | All students whose names start with “A”. |
| SELECT * FROM books WHERE title LIKE '%Java%'; | All books that contain the word “Java” anywhere in the title. |
| SELECT * FROM employees WHERE id LIKE 'E_5'; | All employee IDs that start with “E”, have any single character, and end with “5”. |
🧩 Practice Problems
- Write a query to find all customers whose email ends with “@gmail.com”.
- Find all product codes that have exactly 8 characters and start with “P”.
- Retrieve all records where the description contains the word “urgent” (case‑insensitive).
Exam Tip: Remember that LIKE is case‑insensitive in most databases, but ILIKE is used in PostgreSQL for explicit case‑insensitivity. Always test your wildcard patterns to avoid unexpected matches.
💡 Analogy: Wildcards as a Treasure Map
Imagine you’re looking for buried treasure. The % is like a wide net that can catch any number of clues, while the _ is a single stepping stone that must be exactly one step away. By combining them, you can craft a map that leads straight to the treasure (the data you want).
Quick Check: If you want to find all names that are exactly five letters long, what query would you write?
Revision
Log in to practice.