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.

Example

#

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).

  • % (percent) matches any string with zero or more characters.
  • _ (underscore) matches any single character.

More Examples

WHERE LIKE %

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
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

WHERE LIKE _

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
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

You may also like



Guides


vsn 3.1