Below is a sample table of Indian people with their names, cities, and states. This table is used to demonstrate how SQL Wildcard characters are applied.
| ID | Name | City | State |
|---|---|---|---|
| 1 | Amit Kumar | Delhi | Delhi |
| 2 | Anjali Verma | Mumbai | Maharashtra |
| 3 | Manish Yadav | Mumbai | Maharashtra |
| 4 | Madhuri Joshi | Bhopal | Madhya Pradesh |
| 5 | Mohan | Jaipur | Rajasthan |
You can use SQL wildcards to search for patterns in data. Below are some SQL queries that use wildcards:
SELECT * FROM IndianPeople WHERE Name LIKE 'A%';
SELECT * FROM IndianPeople WHERE Name LIKE 'A__%';
SELECT * FROM IndianPeople WHERE Name LIKE '[AMP]%';
SELECT * FROM IndianPeople WHERE Name LIKE '[A-C]%';
SELECT * FROM IndianPeople WHERE Name LIKE 'A__%';
Learn how to use SQL wildcards to search for patterns in your database.
A wildcard character is used to substitute one or more characters in a string. Wildcard characters are often used with the LIKE operator in SQL, which allows you to search for patterns in text-based columns.
Below is a brief overview of the most common wildcard characters:
The % wildcard represents zero or more characters. It can be used to match any string that contains the specified pattern. For example, to search for customers whose name contains the letter ‘a’, we can use the following query:
SELECT * FROM IndianPeople WHERE CustomerName LIKE '%a%';
Explanation: This query will return all customers whose name contains the letter ‘a’ at any position.
Example Output:
ID | Name | City | State
----------------------------------------
1 | Anand Kumar | Mumbai | Maharashtra
3 | Neelam Gupta | Pune | Maharashtra
5 | Anjali Sharma | Delhi | Delhi
The _ wildcard represents exactly one character. It is useful when you need to match a single character in a specific position in a string. For example, to search for cities that end with “on”, we can use the following query:
SELECT * FROM IndianPeople WHERE City LIKE '_on';
Explanation: This query will return cities that end with “on” and have exactly one character before the “on”.
Example Output:
ID | Name | City | State
----------------------------------------
2 | Anil Kumar | Kanpur | Uttar Pradesh
4 | Mohan Singh | Patan | Gujarat
The [] wildcard matches any single character within the brackets. You can specify a set of characters or a range to match. For example, to search for customers whose names start with either “A”, “M”, or “R”, use the following query:
SELECT * FROM IndianPeople WHERE CustomerName LIKE '[AMR]%';
Explanation: This query will return all customers whose name starts with “A”, “M”, or “R”.
Example Output:
ID | Name | City | State
----------------------------------------
1 | Anand Kumar | Mumbai | Maharashtra
2 | Mohan Sharma | Delhi | Delhi
3 | Ramesh Gupta | Jaipur | Rajasthan
The – wildcard allows you to specify a range of characters to match. For example, to search for customers whose name starts with any letter between “A” and “F”, you can use the following query:
SELECT * FROM IndianPeople WHERE CustomerName LIKE '[A-F]%';
Explanation: This query will return customers whose name starts with any letter between “A” and “F”.
Example Output:
ID | Name | City | State
----------------------------------------
1 | Anand Kumar | Mumbai | Maharashtra
4 | Neelam Gupta | Pune | Maharashtra