SQL WHERE LIKE Statement
When do I use WHERE LIKE in SQL?
Use WHERE LIKE when only a fragment of a text value is known.
The WHERE LIKE clause determines if a character string matches a pattern.
WHERE LIKE supports two wildcard match options: % and _.
The SQL WHERE LIKE syntax
The general syntax is
SELECT column-names FROM table-name WHERE column-name LIKE value
Wildcard characters allowed in 'value' are % (percent) and _ (underscore).
- % (percent) matches any string with zero or more characters.
- _ (underscore) matches any single character.
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
SQL WHERE LIKE Examples
Problem: List all products with names that start with 'Ca'
SELECT Id, ProductName, UnitPrice, Package FROM Product WHERE ProductName LIKE 'Ca%'
Result: 2 records.
Id | ProductName | UnitPrice | Package |
---|---|---|---|
18 | Carnarvon Tigers | 62.50 | 16 kg pkg. |
60 | Camembert Pierrot | 34.00 | 15-300 g rounds |
PRODUCT |
---|
Id |
ProductName |
SupplierId |
UnitPrice |
Package |
IsDiscontinued |
Problem: List all products that start with
'Cha' or 'Chan' and have one more character.
'Cha' or 'Chan' and have one more character.
SELECT Id, ProductName, UnitPrice, Package FROM Product WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'
Result: 2 records.
Id | ProductName | UnitPrice | Package |
---|---|---|---|
1 | Chai | 18.00 | 10 boxes x 20 bags |
2 | Chang | 19.00 | 24 - 12 oz bottles |