Wildcards in MySql
Wildcards in MySQL
Wildcards in MySQL are used with the LIKE operator to search for patterns in text. They help find records where a column value matches a specific pattern.
1. Wildcard Characters in MySQL
| Wildcard | Description | Example |
|---|---|---|
% (Percent) | Represents zero, one, or multiple characters | 'A%' (Starts with 'A') |
_ (Underscore) | Represents exactly one character | 'A_' (Starts with 'A' and has one more character) |
[charlist] | Matches any single character in a list | '[abc]%' (Starts with 'a', 'b', or 'c') |
[^charlist] or [!charlist] | Matches any single character NOT in a list | '[^abc]%' (Does NOT start with 'a', 'b', or 'c') |
🔹 Note: [] and ^ are not supported in MySQL, but are available in other databases like SQL Server.
2. Using % Wildcard (Multiple Characters)
SELECT * FROM employees WHERE name LIKE 'A%';
✅ Finds all names that start with "A" (e.g., Alice, Alan, Andrew).
SELECT * FROM employees WHERE name LIKE '%son';
✅ Finds names ending with "son" (e.g., Jackson, Wilson).
SELECT * FROM employees WHERE name LIKE '%an%';
✅ Finds names that contain "an" anywhere (e.g., Jonathan, Daniel).
3. Using _ Wildcard (Single Character)
SELECT * FROM employees WHERE name LIKE 'J_n';
✅ Finds names like "Jon", "Jen", "Jan", but NOT "John".
SELECT * FROM employees WHERE name LIKE 'A__e';
✅ Finds names with "A" as the first letter and "e" as the fourth letter (e.g., "Anne").
4. Using NOT LIKE
SELECT * FROM employees WHERE name NOT LIKE 'A%';
✅ Finds names that do NOT start with "A".
5. Using LIKE with AND & OR
SELECT * FROM employees WHERE name LIKE 'A%' OR name LIKE '%n';
✅ Finds names that start with "A" OR end with "n".
SELECT * FROM employees WHERE name LIKE 'A%' AND name LIKE '%e';
✅ Finds names that start with "A" AND end with "e" (e.g., "Alice").
6. Using Wildcards with ORDER BY
SELECT * FROM employees WHERE name LIKE 'A%' ORDER BY name ASC;
✅ Finds names starting with "A" and sorts them alphabetically.
Key Takeaways
✅ % – Matches any number of characters (including zero).
✅ _ – Matches exactly one character.
✅ LIKE is used for pattern matching, while NOT LIKE excludes patterns.
✅ Use wildcards wisely, as they can slow down large database queries.
