SQL Wildcards
SQL wildcards can be used when searching for data in a database.
SQL Wildcards
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
| Wildcard |
Description |
| % |
A substitute for zero or more characters |
| _ |
A substitute for exactly one character |
| [charlist] |
Any single character in charlist |
| [^charlist]
or
[!charlist] |
Any single character not in charlist |
SQL Wildcard Examples
We have the following "Persons" table:
| P_Id |
LastName |
FirstName |
Address |
City |
| 1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
| 3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
Using the % Wildcard
Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE 'sa%' |
The result-set will look like this:
| P_Id |
LastName |
FirstName |
Address |
City |
| 1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE City LIKE '%nes%' |
The result-set will look like this:
| P_Id |
LastName |
FirstName |
Address |
City |
| 1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
Using the _ Wildcard
Now we want to select the persons with a first name that starts with any character, followed by "la"
from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE FirstName LIKE '_la' |
The result-set will look like this:
| P_Id |
LastName |
FirstName |
Address |
City |
| 1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
Next, we want to select the persons with a last name that starts with "S",
followed by any character, followed by "end", followed by any character,
followed by "on" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on' |
The result-set will look like this:
| P_Id |
LastName |
FirstName |
Address |
City |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
Using the [charlist] Wildcard
Now we want to select the persons with a last name that starts with "b" or "s" or "p"
from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%' |
The result-set will look like this:
| P_Id |
LastName |
FirstName |
Address |
City |
| 2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
| 3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
Next, we want to select the persons with a last name that do not start with
"b" or "s" or "p" from the "Persons" table.
We use the following SELECT statement:
SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%' |
The result-set will look like this:
| P_Id |
LastName |
FirstName |
Address |
City |
| 1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
| 
