Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

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



Earn income with your data and sql skills
Sign up and we'll send you the best freelance opportunities straight to your inbox.
We're building the largest self-service freelancing marketplace for people like you.

Guides


vsn 3.1