LIKE and pattern matching

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. Equality finds exact text. LIKE finds text that follows a pattern, such as every product name beginning with Desk.

Run the example

SELECT ProductID, ProductName
FROM dbo.Products
WHERE ProductName LIKE N'Desk%'
ORDER BY ProductID;

Read it step by step

1. ProductName LIKE N'Desk%' compares each name with a pattern. Desk is a literal prefix.
2. The % wildcard stands for zero or more characters, so Desk Lamp and Desk Chair both match.
3. ORDER BY ProductID gives a repeatable two-row result. The _ wildcard, by contrast, stands for exactly one character.

Check the result

There are two matching products on dataset v1.0.0. A pattern of N'%Desk%' would mean contains Desk anywhere, while N'Desk%' specifically means starts with Desk. The database collation determines whether letter case matters.

Practice

Find customer first names beginning with A. Which records in the unmodified v1.0.0 dataset match?

SELECT CustomerID, FirstName FROM dbo.Customers WHERE FirstName LIKE N'A%' ORDER BY CustomerID;