Execute the following script in Query Editor to demonstrate the usage of CASE function in a WHERE clause:

USE AdventureWorks
GO

SELECT Category=c.Name,
Subcategory=sc.Name,
ProductName = p.Name,
ProductNumber,
Color = isnull(Color,''),
Cost = '$'+convert(varchar,StandardCost,1),
ListPrice = '$'+convert(varchar,ListPrice,1),
ProductLine
FROM Production.Product p
JOIN Production.ProductSubcategory sc
ON p.ProductSubcategoryID=sc.ProductSubcategoryID
JOIN Production.ProductCategory c
ON sc.ProductCategoryID = c.ProductCategoryID
WHERE p.Name like (
CASE ProductLine
WHEN 'R' THEN 'Road%'
WHEN 'M' THEN 'Mountain%'
WHEN 'T' THEN 'Touring%'
WHEN 'S' THEN '%Shorts%'
ELSE 'Not for sale%'
END)
ORDER BY Category, Subcategory, ProductName;
GO