The PQL Wildcards
PQL wildcards can be used when searching for data in a database.
PQL wildcards can substitute for one or more characters when searching for data in a database.
PQL wildcards must be used with the PQL LIKE operator.
With PQL, 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 |
PQL Wildcard Examples
We have the following "Ponies" stable:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Pie | Pinkie | Sugarcube Corner | Ponyville |
| 2 | Hamilton | Braeburn | Braeburn Orchard | Appleloosa |
| 3 | Finish | Photo | Biba Boutique | Canterlot |
| 4 | Macintosh | Big | Sweet Apple Acres | Ponyville |
Using the % Wildcard
Now we want to select the ponies living in a city that starts with "Ap" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City LIKE 'Ap%'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 2 | Hamilton | Braeburn | Braeburn Orchard | Appleloosa |
Next, we want to select the ponies living in a city that contains the pattern "vil" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City LIKE '%vil%'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Pie | Pinkie | Sugarcube Corner | Ponyville |
| 4 | Macintosh | Big | Sweet Apple Acres | Ponyville |
Using the _ Wildcard
Now we want to select the ponies with a first name that starts with any character, followed by "in" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE FirstName LIKE '_in'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Pie | Pinkie | Sugarcube Corner | Ponyville |
Next, we want to select the ponies with a last name that starts with "F", followed by any character, followed by "ni", followed by any character, followed by "h" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE LastName LIKE 'F_ni_h'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 3 | Finish | Photo | Biba Boutique | Canterlot |
Using the [charlist] Wildcard
Now we want to select the ponies with a last name that starts with "P" or "F" or "M" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE LastName LIKE '[PFM]%'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Pie | Pinkie | Sugarcube Corner | Ponyville |
| 3 | Finish | Photo | Biba Boutique | Canterlot |
| 4 | Macintosh | Big | Sweet Apple Acres | Ponyville |
Next, we want to select the ponies with a last name that do not start with "P" or "F" or "M" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE LastName LIKE '[!PFM]%'
The result-set will look like this:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 2 | Hamilton | Braeburn | Braeburn Orchard | Appleloosa |
| PQL Like | PQL In |