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:
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:
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:
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:
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:
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:
WHERE LastName LIKE '[!bsp]%'
The result-set will look like this:
P_Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Hansen | Ola | Timoteivn 10 | Sandnes |