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 |
|