Using Wildcards in SQL
In SQL, wildcards are special symbols used within the LIKE operator to search for patterns in text data. These wildcards allow you to perform flexible and powerful searches, making it easier to locate records based on partial matches, rather than requiring exact matches. Wildcards are most commonly used with the LIKE keyword to filter results from queries.
In this chapter, we will explore the concept of wildcards in SQL, starting with basic usage, then moving on to advanced techniques, examples, and best practices. By the end of this chapter, you will have a complete understanding of how to leverage wildcards to perform efficient and flexible searches.
What Are Wildcards in SQL?
Wildcards are symbols used to substitute one or more characters in a string. They allow you to search for patterns in columns that contain text data, such as names, email addresses, product descriptions, etc.
In SQL, there are two main wildcards:
%(Percent Sign): Represents zero or more characters._(Underscore): Represents a single character.
Basic Syntax of Wildcards in SQL
Wildcards are typically used with the LIKE operator in a WHERE clause. Here’s the basic syntax for using wildcards with LIKE:
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern;
column_name: The name of the column you want to search.table_name: The table that contains the data.pattern: A pattern to search for, which can include wildcards.
The % Wildcard
The % wildcard represents zero or more characters. This makes it useful for finding patterns in text data, such as words that start with, end with, or contain certain letters.
Example 1: Searching for Names That Start with “A”
To find all customers whose names start with “A”:
SELECT name
FROM customers
WHERE name LIKE 'A%';
Output:
| Name |
|---|
| Alice Smith |
| Aaron Brown |
| Amanda White |
The % after the letter “A” means that any sequence of characters (including no characters) can follow “A.”
Example 2: Searching for Names That End with “son”
To find all customers whose names end with “son”:
SELECT name
FROM customers
WHERE name LIKE '%son';
Output:
| Name |
|---|
| Jason Anderson |
| Mason Johnson |
The % before “son” means that any sequence of characters can precede “son.”
Example 3: Searching for Names That Contain “ar”
To find all customers whose names contain the letters “ar”:
SELECT name
FROM customers
WHERE name LIKE '%ar%';
Output:
| Name |
|---|
| Barbara Wright |
| Mark Spencer |
The % wildcard on both sides of “ar” means that “ar” can appear anywhere in the name.
The _ Wildcard
The _ wildcard represents exactly one character. This is useful for finding records that match a specific pattern with a fixed number of characters.
Example 4: Finding Names with Exactly Five Letters Starting with “J”
To find all customers whose names are exactly five letters long and start with “J”:
SELECT name
FROM customers
WHERE name LIKE 'J____';
Output
| Name |
|---|
| Jason |
| Julie |
The four underscores (____) represent exactly four characters that follow the “J.”
Example 5: Finding Phone Numbers with a Specific Pattern
If you have a phone_number column, you can use _ to match phone numbers with a specific pattern. For example, to find phone numbers where the area code is “555”:
SELECT phone_number
FROM customers
WHERE phone_number LIKE '555-___-____';
Output:
| Phone Number |
|---|
| 555-123-4567 |
| 555-987-6543 |
The underscores (_) represent the digits in the phone number, and the dashes (-) are treated as literal characters.
Combining Wildcards with Other SQL Clauses
Wildcards can be combined with other SQL clauses, such as AND, OR, and NOT, to create more complex search conditions.
Example 6: Combining Wildcards with AND
To find customers whose names start with “A” and who live in “New York”:
SELECT name
FROM customers
WHERE name LIKE 'A%' AND city = 'New York';
Output:
| Name |
|---|
| Alice Smith |
This query returns customers whose names start with “A” and live in New York.
Example 7: Combining Wildcards with NOT LIKE
The NOT LIKE operator is used to exclude records that match a certain pattern. For example, to find all customers whose names do not start with “A”:
SELECT name
FROM customers
WHERE name NOT LIKE 'A%';
Output:
| Name |
|---|
| Brian Jones |
| Christine Lee |
This query returns customers whose names do not start with “A.”
Advanced Pattern Matching with Wildcards
Example 8: Using Wildcards in the Middle of a Pattern
You can place wildcards anywhere within a pattern. For example, to find product names that contain “Pro” followed by any sequence of characters:
SELECT product_name
FROM products
WHERE product_name LIKE '%Pro%';
Output:
| Product Name |
|---|
| MacBook Pro |
| GoPro Hero |
| iPhone Pro Max |
This query returns products that have “Pro” anywhere in the product name.
Example 9: Matching Specific Character Lengths
You can use a combination of % and _ to match strings of specific lengths. For example, to find email addresses that have exactly three letters in the domain name:
SELECT email
FROM users
WHERE email LIKE '%@___.com';
Output:
| john@abc.com |
| mary@xyz.com |
This query matches email addresses with exactly three characters before .com.
Wildcards and Case Sensitivity
The behavior of wildcards can vary depending on the database system in use. In most databases, LIKE is case-insensitive, meaning it does not distinguish between uppercase and lowercase letters. However, some databases may require additional settings to make LIKE case-insensitive or case-sensitive.
Example 10: Case-Insensitive Search
In case-insensitive databases, the following query will return names that start with either “a” or “A”:
SELECT name
FROM customers
WHERE name LIKE 'a%';
Output:
| Name |
|---|
| Alice Smith |
| Aaron Brown |
Escaping Wildcard Characters
Sometimes, the pattern you’re searching for may include literal % or _ characters, which would normally be treated as wildcards. To search for these literal characters, you can use an escape character.
Example 11: Searching for a Literal %
To find product names that contain a literal % (such as “50% Off”):
SELECT product_name
FROM products
WHERE product_name LIKE '%\%%' ESCAPE '\';
Output:
| Product Name |
|---|
| 50% Off Deals |
The backslash (\) is used as an escape character to treat % as a literal character.
Performance Considerations When Using Wildcards
Wildcards provide flexibility in searching, but they can also affect query performance, especially in large datasets. Here are some tips to optimize wildcard queries:
- Use Indexed Columns: Ensure that the column you’re searching with
LIKEis indexed. - Avoid Leading Wildcards: Using
%at the beginning of a pattern (e.g.,%pattern) can cause a full table scan, which slows down performance. Try to use leading wildcards sparingly. - Full-Text Search: For very large text fields, consider using full-text search features provided by your database system, which are optimized for text searches.
Wildcards in SQL offer a flexible and powerful way to search for patterns in text data. By understanding how to use the % and _ wildcards effectively, you can create queries that search for partial matches, specific character lengths, and more. Whether you're looking for names that start with certain letters, email addresses with specific domains, or products that contain keywords, wildcards provide a simple yet effective solution. Happy coding !❤️