A wildcard character is used to replace any character(s) in a string. Wildcard operator are used with SQL Like operator . The link to SQL WHERE Clause in mentioned in the article. Like operator are used with SQL WHERE CLAUSE to search for a particular pattern mentioned in the table/column.
SQL Wildcard operators—> [SQL Like opearator+ SQL WHERE CLAUSE]
Wildcard operators deals with 4 different types of operators:
| Operators | Description |
|---|---|
| % | This operator represents zero or more characters |
| _ | This operator represents a single character in a string |
| [range of characters] | It defines set of characters and gives a range of characters to match |
| [!range_character] or[^range_character] | Defines sets and ranges of character not to match |
Note1 : MS Access uses a “?”(question mark) symbol instead of “_”(underscore) symbol.
Note2: Combination of Wildcard operators are feasible. “%”, “_” can be used together.
Wildcard operators are very often used in large databases. Combination of LIKE operator and Wildcard proves to be very effective .
Examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:
| LIKE Operator | Description |
|---|---|
| WHERE name/example LIKE ‘h%’ | Helps in finding the value that start with ‘h’ |
| WHERE name/example LIKE ‘%h’ | Find values that end with ‘h’ |
| WHERE name/example LIKE ‘%singh%’ | Find values that have singh in it |
| WHERE name/example LIKE ‘_i%’ | Helps in finding the values that have “i” in 2 pos. |
| WHERE name/example LIKE ‘s_%_%’ | Find any values with atleast ‘3’ char as len. and start with ‘s’ |
| WHERE name/example LIKE ‘s%h’ | Values starting with ‘s’ and ends with ‘h’ are to be picked |
Therfore, we saw the examples of combination of WHERE clause and LIKE operator using Wildcard operator. [Wildcard operators- “%”,”_”]
Different Views of Wildcard operators
Let us take a deep dive ,and explore Wildcard operators using different examples.
Demo Database: Padhle
Table Name:Employee
| EmployeeID | Name | City | Pincode |
|---|---|---|---|
| 1 | Harsh | Mumbai | 303007 |
| 2 | Harshit | Srinagar | 690390 |
| 3 | Sunny | Alberta | 696909 |
| 4 | Young thug | michigan | 1010109 |
| 5 | Anuj | Varanasi | 2090789 |
Using the % Wildcard
Syntax:
SELECT */column_name FROM 'Table_name' WHERE 'Column_name' LIKE 'Wildcard operator'
Example1:
SELECT City FROM Employee WHERE City LIKE 'Va%';
The following SQL statement selects City with name starting from “Va”:
| City |
|---|
| Varanasi |
Example 2:
SELECT Name from Employee WHERE Name LIKE '%ars%';
The following SQL statement selects Employee with Name containing the pattern-‘ars’.
| Name |
|---|
| Harsh |
| Harshit |
Using the _ Wildcard
Syntax:
SELECT */column_name FROM 'Table_name' WHERE 'Column_name' LIKE 'Wildcard operator'
This operator represents a single character in a string.
Example 1:
SELECT * FROM Employee WHERE pincode LIKE '_007';
The following statement selects all rows from Employee with a pincode starting with any digit,followed by’007′.
| EmployeeID | Name | City | Pincode |
|---|---|---|---|
| 3 | Sunny | Alberta | 303007 |
Example 2:
SELECT * FROM Employee WHERE pincode LIKE '69_9_9';
The following statement will search for the pincode with ’69’ as starting number ,followed by any character,followed by ‘9 ‘,followed by any character,then followed by ‘9’.
| EmployeeID | Name | City | Pincode |
|---|---|---|---|
| 1 | Harsh | Mumbai | 696909 |
Using the [range of characters/charlist]
Syntax:
SELECT */column_name FROM 'Table_name' WHERE 'Column_name' LIKE 'Wildcard operator'
Example 1:
SELECT * FROM Employee WHERE City LIKE '[MSA]%';
The following SQL statement selects all with a City starting with “M”, “S”, or “A”:
| EmployeeID | Name | City | Pincode |
|---|---|---|---|
| 1 | Harsh | Mumbai | 303007 |
| 2 | Harshit | Srinagar | 690390 |
| 3 | Sunny | Alberta | 696909 |
Using the [!range_character/charlist]
Syntax:
SELECT */column_name FROM 'Table_name' WHERE 'Column_name' LIKE 'Wildcard operator'
Example1:
SELECT * FROM Employee WHERE Name LIKE '[!SAH]%';
The following SQL statement selects all the pincode where ‘Name’ is not starting from ‘S’,’A’,’H’.
| EmployeeID | Name | City | Pincode |
|---|---|---|---|
| 4 | Young thug | michigan | 1010109 |
Example 2:
SELECT * FROM Customers WHERE City NOT LIKE '[SAH]%';
Note: NOT LIKE also serves the same purpose and output will be same for both the cases.