18 Databases – Wildcard Searches
What are Wildcards?
Wildcards are special characters that represent one or more unknown characters in a search pattern. They allow you to find records that match a partial value rather than an exact value.
SQL Wildcards
- % – Matches any sequence of zero or more characters.
- _ – Matches exactly one character.
Using Wildcards in SQL Queries
- Write a
SELECT statement that includes a WHERE clause with the LIKE operator. - Insert the wildcard pattern after the
LIKE keyword. - Execute the query to view the matching records.
Examples of SQL Wildcard Patterns
| Pattern | Matches |
|---|
| ‘A%’ | All names that start with “A” (e.g., Alice, Andrew) |
| ‘%son’ | All names that end with “son” (e.g., Johnson, Wilson) |
| ‘_a%’ | All names where the second character is “a” (e.g., “Bailey”, “Catherine”) |
| ‘%e_%’ | All names that contain “e” followed by any single character (e.g., “Peter”, “George”) |
Wildcard Searches in Microsoft Access
- * – Matches any sequence of characters.
- ? – Matches exactly one character.
- # – Matches a single digit.
- ! – Matches any single character that is not a digit.
Using Wildcards in Access Queries
- Create a new query in Design \cdot iew.
- In the
Criteria row of the field you wish to search, type the pattern using Access wildcards. - Run the query to see the results.
Practical Tips
- Wildcards are case-insensitive in most SQL databases.
- Using wildcards at the beginning of a pattern (e.g.,
%abc) can slow down queries because the database cannot use an index efficiently. - Escape characters (e.g.,
ESCAPE '\' ) can be used if you need to search for the wildcard symbols themselves.
Practice Questions
- Write an SQL query to find all customer names that contain the letter “x” anywhere in the name.
- In Access, create a query that returns all product codes that start with “P” followed by any two digits.
- Explain why using
LIKE '%abc%' might be less efficient than LIKE 'abc%'. - Write a MySQL query that finds all email addresses ending with “@example.com”.
- In PostgreSQL, how would you search for a string that contains an underscore character?
Key Takeaways
- Wildcards enable flexible searching in databases.
- SQL uses
% and _ as wildcards; Access uses *, ?, #, and !. - Wildcards can improve user experience but may impact performance if used at the start of a pattern.
- Always test your wildcard queries to ensure they return the expected results.