MySQL Wildcard Characters

In MySQL, wildcard characters are special symbols used with the LIKE operator in SQL queries to search for a specified pattern in a column. These wildcard characters allow for flexible and powerful pattern matching when querying data.

Wildcard Characters in MySQL

1. Percent sign (%)

• Represents zero or more characters.
• It can match any sequence of characters, including no characters at all.

Example:

• bl% will find values like bl, black, blue, and blob.

2. Underscore (_)

• Represents a single character.
• It is used when you want to match exactly one character at a specific position in the string.

Example:

• h_t will find values like hot, hat, and hit.

These wildcard characters can be used alone or in combination to form more complex patterns.

Examples of LIKE Operators with Wildcards

Here are some common examples of how the LIKE operator works with wildcard characters:

1. Finds values starting with a specific character

WHERE CustomerName LIKE 'a%'

• Description: This query finds all CustomerName entries that start with the letter "a".
• Example Matches: Alfreds Futterkiste, Anna, Antonio

2. Finds values ending with a specific character

WHERE CustomerName LIKE '%a'

• Description: This query finds all CustomerName entries that end with the letter "a".
• Example Matches: Maria, Anna, Alfreda

3. Finds values containing a specific substring

WHERE CustomerName LIKE '%or%'

• Description: This query finds all CustomerName entries that contain "or" anywhere in the name.
• Example Matches: George, Boris, Morris

4. Finds values where a specific character is at a certain position

WHERE CustomerName LIKE '_r%'

• Description: This query finds all CustomerName entries where the second character is "r".
• Example Matches: George, Fred

5. Finds values starting with a specific character and having at least three characters

WHERE CustomerName LIKE 'a_%_%'

• Description: This query finds all CustomerName entries that start with "a" and are at least 3 characters long.
• Example Matches: Anna, Alfred, Antonio

6. Finds values starting with a specific character and ending with another

WHERE ContactName LIKE 'a%o'

• Description: This query finds all ContactName entries that start with "a" and end with "o".
• Example Matches: Antonio, Alberto

Using the % Wildcard

The % wildcard is useful when you want to match any sequence of characters. Here are some practical examples:

1. Find all customers with a City starting with "ber"

SELECT * FROM Customers
WHERE City LIKE 'ber%';

• Description: This query selects all customers whose city starts with "ber".
• Example Matches: Berlin, Bern, Berg

2. Find all customers with a City containing "es"

SELECT * FROM Customers
WHERE City LIKE '%es%';

• Description: This query selects all customers whose city contains the substring "es".
• Example Matches: Manchester, Brussels, Vancouver

Using the _ Wildcard

The _ wildcard allows you to specify that a single character can appear in a particular position.

1. Find all customers with a City starting with any character, followed by "ondon"

SELECT * FROM Customers
WHERE City LIKE '_ondon';

• Description: This query selects all customers whose city name matches "_ondon", where the first character can be anything.
• Example Matches: London, Mondon

2. Find all customers with a City starting with "L", followed by any character, then "n", then any character, and then "on"

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

• Description: This query finds all customers whose city name starts with "L", has any character in the second position, then contains "n", and ends with "on".
• Example Matches: London, Linton

Conclusion

The LIKE operator in MySQL is a powerful tool for matching patterns in string data. By using the % and _ wildcard characters, you can easily perform searches based on partial information. These wildcards allow for flexible querying, making it easier to filter records with specific patterns and conditions.