Learn how to use MySQL wildcards for efficient fuzzy queries. This guide covers the usage of % and _ wildcards, query examples, and performance tips to optimize data filtering.

This article will provide a detailed introduction to how to use wildcards for filtering in MySQL, with clear steps and specific examples to help you master this useful technique.

In MySQL, wildcards are powerful tools for performing fuzzy matching in string queries. Using wildcards, we can filter data flexibly in the WHERE clause without requiring exact matches. The most common wildcards are % and _, which help you efficiently query partially matched data. This article will thoroughly explain how to use wildcards in MySQL filtering, with clear steps and examples to help you master this practical technique.

1. What are Wildcards?

In SQL queries, wildcards are used to perform fuzzy matching of certain strings. The two most common wildcards in MySQL are:

• %: Matches zero or more characters.

• _: Matches a single character.

Wildcards are often used in conjunction with the LIKE operator.

2. Basic Usage of Wildcards

1. Using the % Wildcard

The % wildcard can represent any number of characters (including zero characters). For example, you can use % to find strings that start or end with a specific character, or records containing certain substrings.

Example 1: Querying Strings that Start with a Specific Character

Assume we have a table called users with the following data:

We want to find all users whose username starts with "john". You can use the following SQL query:

SELECT * FROM users WHERE username LIKE 'john%';

Result:

In this query, 'john%' matches all usernames that start with "john", regardless of how many characters follow.

Example 2: Querying Strings Containing a Specific Substring

If you want to find records containing a particular substring, you can use % to allow for any characters before or after the substring.

SELECT * FROM users WHERE username LIKE '%smith%';

Result:

In this query, '%smith%' matches all usernames containing "smith".

Example 3: Querying Strings that End with a Specific Character

Suppose you want to find all email addresses ending with "@example.com". You can use the following query:

SELECT * FROM users WHERE email LIKE '%@example.com';

Result:

Here, '%@example.com' matches all email addresses ending with "@example.com".

2. Using the _ Wildcard

The _ wildcard matches a single character. You can use it to precisely control the number of characters matched. For example, if you want to find all usernames where the second character is "a", you can use _ to specify the character position.

Example 4: Querying Strings with a Specific Character in a Certain Position

Assume the following data, where we want to find records with the second character of the username being "a":

SELECT * FROM users WHERE username LIKE '_a%';

Result:

In this query, '_a%' matches all usernames where the second character is "a", regardless of the characters that follow.

Example 5: Querying Strings with a Specific Length

You can use multiple _ characters to specify the exact length of the string. For example, the following query finds all usernames with exactly five characters:

SELECT * FROM users WHERE username LIKE '_____';

Result:

Here, '_____' represents matching usernames with exactly 5 characters.

3. Combining % and _ for More Complex Matching

You can also combine % and _ to perform more complex matches. For example, to query usernames where the third character is "e" and the last character is "e", you can use:

SELECT * FROM users WHERE username LIKE '__e%e';

Result:

Here, '__e%e' matches usernames where the third character is "e" and the last character is "e".

3. Case Sensitivity

By default, the LIKE operator in MySQL is case-sensitive, depending on the column's character set and collation. For example, when using the default latin1_swedish_ci collation, LIKE is case-sensitive. You can change the matching behavior by modifying the character set or using the COLLATE clause.

Example 6: Performing a Case-Insensitive Match

SELECT * FROM users WHERE username LIKE 'john%' COLLATE utf8_general_ci;

This forces a case-insensitive match.

4. Considerations for Using Wildcards for Data Filtering

• Performance Impact: Using the % wildcard, especially at the beginning of a string (e.g., '%abc'), can negatively impact query performance because the database cannot optimize the query using indexes. When designing tables and queries, try to avoid using % at the start of the string.

• Avoiding Wildcard Abuse: While wildcards are very flexible, you should avoid overly complex fuzzy queries, especially with large datasets, as they can lead to performance degradation.

• Using Indexes: If you want to optimize your queries and improve performance, consider creating indexes on frequently filtered columns, especially for fields used in LIKE clauses.

Conclusion

By using the LIKE operator with wildcards (% and _), we can perform flexible string matching operations in MySQL, greatly enhancing the flexibility and functionality of our queries. When using these wildcards, it's important to consider query performance, avoid starting a string with %, and properly use indexes to optimize queries. By understanding and mastering MySQL wildcards and fuzzy queries, you will be able to filter and select data more efficiently.