SQL WHERE LIKE

WHERE LIKE determines if a string matches a pattern.

WHERE LIKE supports two wildcard options: % and _.

Use WHERE LIKE when only a fragment of a text value is known.

List all products that are packaged in jars.

SELECT *
  FROM Product
 WHERE Package LIKE '%jars%'

Result: 8 records

Id	ProductName	SupplierId	UnitPrice	Package
4	Chef Anton's Cajun Seasoning	2	22.00	48 - 6 oz jars
6	Grandma's Boysenberry Spread	3	25.00	12 - 8 oz jars
8	Northwoods Cranberry Sauce	3	40.00	12 - 12 oz jars
10	Ikura	4	31.00	12 - 200 ml jars
36	Inlagd Sill	17	19.00	24 - 250 g jars
63	Vegie-spread	7	43.90	15 - 625 g jars
66	Louisiana Hot Spiced Okra	2	17.00	24 - 8 oz jars
73	Röd Kaviar	17	15.00	24 - 150 g jars

Syntax

LIKE syntax.

SELECT column-names
  FROM table-name
 WHERE column-name LIKE value

Wildcard characters allowed in value are % (percent) and _ (underscore).

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

Problem: List all products that start with '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

Feedback

Guides

On this page