What is LIKE

The final comparison operator can only be used for character data and allows us to use wildcard characters and regular expression patterns. Wildcards allow us to specify partial strings. For example, you could use the following query to return all products with names that contain the word “mountain”:

SQLCopy

SELECT Name, ListPrice
FROM SalesLT.Product
WHERE Name LIKE '%mountain%';

The % wildcard represents any string of 0 or more characters, so the results include products with the word “mountain” anywhere in their name, like this:

Name

ListPrice

Mountain Bike Socks, M

9.50

Mountain Bike Socks, L

9.50

HL Mountain Frame – Silver, 42

1364.0

HL Mountain Frame – Black, 42

1349.60

HL Mountain Frame – Silver, 38

1364.50

Mountain-100 Silver, 38

3399.99

You can use the _ (underscore) wildcard to represent a single character, like this:

SQLCopy

SELECT ProductName, ListPrice
FROM SalesLT.Product
WHERE ProductName LIKE 'Mountain Bike Socks, _';

The following results only include products that begin with “Mountain Bike Socks, ” and a single character after:

ProductName

ListPrice

Mountain Bike Socks, M

9.50

Mountain Bike Socks, L

9.50

You can also define complex patterns for strings that you want to find. For example, the following query searched for products with a name that starts with “Mountain-“, then followed by:

• three characters between 0 and 9
• a space
• any string
• a comma
• a space
• two characters between 0 and 9

SQLCopy

SELECT ProductName, ListPrice
FROM SalesLT.Product
WHERE ProductName LIKE 'Mountain-[0-9][0-9][0-9] %, [0-9][0-9]';

The results from this query might look something like this:

ProductName

ListPrice

Mountain-100 Silver, 38

3399.99

Mountain-100 Silver, 42

3399.99

Mountain-100 Black, 38

3399.99

Mountain-100 Black, 42

3399.99

Mountain-200 Silver, 38

2319.99

Mountain-200 Silver, 42

2319.99

Mountain-200 Black, 38

2319.99

Mountain-200 Black, 42

2319.99