MySQL Wildcards

Introduction

When working with databases, you'll often need to search for data that matches certain patterns rather than exact values. For instance, you might want to find all customers whose names start with 'J' or all products that contain the word 'phone'. This is where wildcards come in handy.

MySQL wildcards are special characters used with the LIKE operator in SQL queries to perform pattern matching. They allow you to create flexible search patterns that can match multiple values based on partial information.

In this tutorial, we'll explore how to use wildcards effectively in your MySQL queries to perform powerful pattern matching operations.

Basic Wildcard Characters

MySQL provides two main wildcard characters for use with the LIKE operator:

1. Percent sign (%) - Represents zero, one, or multiple characters
2. Underscore (_) - Represents exactly one character

Let's look at how each of these works.

The Percent Sign (%) Wildcard

The % wildcard matches any sequence of characters (including zero characters). It's particularly useful when you know only part of the value you're looking for.

Examples:

sql

-- Find all customers whose last name starts with 'S'
SELECT * FROM customers 
WHERE last_name LIKE 'S%';

-- Find all products that end with 'phone'
SELECT * FROM products 
WHERE product_name LIKE '%phone';

-- Find all descriptions that contain the word 'digital'
SELECT * FROM products 
WHERE description LIKE '%digital%';

Example Output:

For the first query, you might get results like:

customer_id	first_name	last_name	email
101	John	Smith	[email protected]
102	Mary	Stevens	[email protected]
105	Alice	Sanders	[email protected]

The Underscore (_) Wildcard

The underscore wildcard _ matches exactly one character. This is useful when you know the length of the text you're searching for or need to match specific positions.

Examples:

sql

-- Find products with a 5-character code (e.g., AB123)
SELECT * FROM products 
WHERE product_code LIKE '_____';

-- Find customers whose first name is 4 letters and starts with 'J'
SELECT * FROM customers 
WHERE first_name LIKE 'J___';

-- Find products with codes that have 'X' as the second character
SELECT * FROM products 
WHERE product_code LIKE '_X___';

Example Output:

For the second query, you might get:

customer_id	first_name	last_name	email
107	Jake	Brown	[email protected]
112	John	Davis	[email protected]
118	Jose	Garcia	[email protected]

Combining Wildcards

You can combine both wildcards in a single pattern to create more complex searches.

sql

-- Find products with 'i' as the second letter and ending with 'one'
SELECT * FROM products 
WHERE product_name LIKE '_i%one';

-- Find emails with exactly 5 characters before the @ symbol
SELECT * FROM customers 
WHERE email LIKE '_____@%.com';

Escaping Wildcard Characters

What if you want to search for actual % or _ characters in your data? Since these are special characters for pattern matching, you need to escape them using the backslash \.

sql

-- Find values that contain a percent sign
SELECT * FROM statistics 
WHERE value LIKE '%\%%';

-- Find text containing an underscore
SELECT * FROM notes 
WHERE note_text LIKE '%\_%';

tip

In some MySQL configurations, you might need to set the ESCAPE clause explicitly:

sql

SELECT * FROM statistics 
WHERE value LIKE '%\%%' ESCAPE '\';

Practical Applications

Let's look at some real-world scenarios where wildcards are particularly useful:

1. Email Validation

sql

-- Find all Gmail users in your customer database
SELECT * FROM customers 
WHERE email LIKE '%@gmail.com';

-- Find users with company domains (usually longer than personal email domains)
SELECT * FROM customers 
WHERE email LIKE '%@%.%.%';

2. Product Search

sql

-- Find all iPhone variants
SELECT * FROM products 
WHERE product_name LIKE 'iPhone%';

-- Find all products with a specific model year
SELECT * FROM products 
WHERE model_number LIKE '%2023%';

3. Address Queries

sql

-- Find all addresses on "Main Street" (accounting for variations)
SELECT * FROM addresses 
WHERE street_address LIKE '%Main St%' OR street_address LIKE '%Main Street%';

-- Find addresses in a specific ZIP code range
SELECT * FROM addresses 
WHERE zip_code LIKE '902__';

Performance Considerations

While wildcards are powerful, be aware of performance implications:

1. Leading wildcards (patterns that begin with % or _) are particularly expensive for databases to process because they prevent the use of indexes.

2. Overuse of wildcards can lead to full table scans, which become problematic with large tables.

For better performance:

sql

-- Less efficient (can't use indexes effectively)
SELECT * FROM products 
WHERE product_name LIKE '%phone%';

-- More efficient if product_name is indexed
SELECT * FROM products 
WHERE product_name LIKE 'phone%';

REGEXP - An Advanced Alternative

For more complex pattern matching than what wildcards can provide, MySQL offers the REGEXP operator (Regular Expressions):

sql

-- Find names that start with 'J' or 'K'
SELECT * FROM customers 
WHERE first_name REGEXP '^[JK]';

-- Find phone numbers in multiple formats
SELECT * FROM customers 
WHERE phone REGEXP '^[0-9]{3}-[0-9]{3}-[0-9]{4}|\\([0-9]{3}\\) [0-9]{3}-[0-9]{4}$';

Summary

MySQL wildcards provide a powerful way to perform pattern matching in your database queries:

• Use the % wildcard to match any number of characters (including zero)
• Use the _ wildcard to match exactly one character
• Combine wildcards to create complex matching patterns
• Escape wildcards with \ when you need to search for the actual characters
• Consider performance impacts, especially with leading wildcards
• For more complex patterns, consider using REGEXP

With these tools, you can create flexible queries that find exactly what you're looking for, even with partial information.

Exercises

To practice using wildcards, try these exercises:

1. Write a query to find all customers whose first name starts with 'A' and last name ends with 'son'.
2. Find all products that have exactly 6 characters in their product code.
3. Find all email addresses from domains that end with '.org' or '.edu'.
4. Create a query that finds all addresses containing a number followed by a street name.
5. Find all records containing a percentage value (e.g., '10%', '25%') in a text field.

Additional Resources

• MySQL Official Documentation on Pattern Matching
• MySQL Regular Expressions for advanced pattern matching

Learning how to effectively use wildcards will significantly enhance your ability to query databases and find exactly the information you need.