SQL wildcards can be used when searching for data in a database.
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 |
Incoming search terms for the article:
SELECT * FROM Persons WHERE FirstName LIKE ?%;, sql charlist, SQL wild cards, sql wildcards, wildcards in oracle sql like charlist [a-z], oracle sql charlist wildcard, SQL IN Operator wildcards, widcards oracle
