SQL Wildcard
When searching for data in a database, you can use SQL wildcards.
SQL Wildcard
When searching for data in a database, SQL wildcards can replace one or more characters.
SQL wildcards must be used with the LIKE operator.
In SQL, the following wildcards can be used:
| Wildcard | Description |
|---|---|
| % | Represents zero or more characters |
| _ | Replace only one character |
| [charlist] | Any single character in the character list |
|
[^charlist] Or [!charlist] |
Any single character not in the character list |
Original table (used in examples):
Persons table:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
Use % wildcard
Example 1
Now, we hope to select the people from the above "Persons" table who live in cities starting with "Ne":
We can use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE 'Ne%'
Result Set:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 2 | Bush | George | Fifth Avenue | New York |
Example 2
Next, we hope to select the people from the "Persons" table who live in cities containing "lond":
We can use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE '%lond%'
Result Set:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London |
Using _ wildcard
Example 1
Now, we hope to select the people from the above "Persons" table whose first name is followed by "eorge":
We can use the following SELECT statement:
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
Result Set:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 2 | Bush | George | Fifth Avenue | New York |
Example 2
Next, we hope to select the record from the "Persons" table where the surname starts with "C", followed by any character, then "r", then any character, then "er":
We can use the following SELECT statement:
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'
Result Set:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 3 | Carter | Thomas | Changan Street | Beijing |
Using [charlist] wildcard
Example 1
Now, we hope to select the cities where the people live from the above "Persons" table with names starting with "A" or "L" or "N":
We can use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE '[ALN]%'
Result Set:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Avenue | New York |
Example 2
Now, we hope to select the cities where the people live from the above "Persons" tableNot starting with People whose names start with "A" or "L" or "N":
We can use the following SELECT statement:
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'
Result Set:
| Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 3 | Carter | Thomas | Changan Street | Beijing |
