Wildcards in SQL are placeholders used to substitute specific values, allowing for greater flexibility in queries. They are essential when you're unsure about the exact values in a dataset, need to search through multiple values, or want to create dynamic queries. There are two main types of wildcards in SQL: percent sign (%) and underscore (_).
The percent sign (%) matches any number of characters, including none. The underscore (_) matches exactly one character. By strategically placing these wildcards in SQL statements, you can retrieve more relevant data efficiently. Let's dive into some SQL wildcard examples to illustrate their usage.
Finding Data with SQL Wildcards
The primary use of wildcards is to search for data when the exact value is unknown. For instance, if you're searching for a customer's last name, but you only know the first few letters, you can use a wildcard to find a match.
Assume we have a table named 'Customers' with a 'LastName' column. Let's find customers with a last name starting with 'Sm'. In MySQL, the query would look like this:
```sql SELECT * FROM Customers WHERE LastName LIKE 'Sm%'; ```
Using the percent sign (%)
The percent sign is the most versatile wildcard, matching any number of characters. In the above example, 'Sm%' matches any last name starting with 'Sm', like 'Smith', 'Smyth', 'Smelser', etc.
You can also use the percent sign to find data containing specific characters anywhere within the string. For instance, to find all customers with 'er' in their last name:
```sql SELECT * FROM Customers WHERE LastName LIKE '%er%'; ```
Using the underscore (_)
The underscore is less versatile; it matches exactly one character. It's handy when you want to match a specific pattern with a placeholder for a single character. For example, to find all customers with a three-letter last name starting with 'S', like 'San', 'Sea', 'Seq', 'Smy':
```sql SELECT * FROM Customers WHERE LastName LIKE 'S_'; ```
Wildcards in Sorting and Grouping
Wildcards aren't limited to SELECT statements; they can also be used in ORDER BY and GROUP BY clauses to sort or group data based on patterns.
For instance, imagine you want to group and display the number of customers by the first letter of their last name. In this case, we'll use the percent sign after the first letter to match any number of characters:
```sql SELECT LEFT(LastName, 1), COUNT(*) FROM Customers GROUP BY LEFT(LastName, 1); ```
Similarly, you can sort data based on patterns. Say you want to sort customers by the second letter of their last name:
```sql SELECT * FROM Customers ORDER BY SUBSTRING(LastName,2,1); ```