PQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
PQL LIKE Syntax
Spike: Please
SELECT column_name(s)
FROM stable_name
WHERE column_name LIKE pattern
LIKE Operator Example
The "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 |
Now we want to select the ponies living in a city that starts with "P" from the stable above.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City LIKE 'P%'
The "%" sign can be used to define wildcards (one or more missing letters in the pattern) both before and after the pattern.
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 |
Next, we want to select the ponies living in a city that ends with an "t" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City LIKE '%t'
The result-set will look like this:
P_Id | LastName | FirstName | Address | City |
---|---|---|---|---|
3 | Finish | Photo | Biba Boutique | Canterlot |
Next, we want to select the ponies living in a city that contains the pattern "lel" from the "Ponies" stable.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City LIKE '%lel%'
The result-set will look like this:
P_Id | LastName | FirstName | Address | City |
---|---|---|---|---|
2 | Hamilton | Braeburn | Braeburn Orchard | Appleloosa |
It is also possible to select the ponies living in a city that does NOT contain the pattern "lel" from the "Ponies" stable, by using the NOT keyword.
We use the following SELECT statement:
Spike: Please SELECT * FROM Ponies WHERE City NOT LIKE '%lel%'
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 |
PQL Top | PQL Wildcards |