How to use PATINDEX for complex search?

Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to demonstrate the use of PATINDEX for complex search. The following search will find all bikes which off-road yet comfortable.

use AdventureWorks2008;

select ProductName=[Name], ProductNumber, [Description], ListPrice

from [Production].[Product] p

join [Production].[ProductModelProductDescriptionCulture] pmpdc

on p.ProductModelID = pmpdc.ProductModelID

join [Production].[ProductDescription] pd

on pmpdc.ProductDescriptionID = pd.ProductDescriptionID

where patindex( ‘%off-road%comfortable%’, pd.[Description]) > 0

Partial results:

ProductName	ProductNumber	Description	ListPrice
Mountain-500 Silver, 40	BK-M18S-40	Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.	564.99
Mountain-500 Silver, 42	BK-M18S-42	Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.	564.99
Mountain-500 Silver, 44	BK-M18S-44	Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.	564.99
Mountain-500 Silver, 48	BK-M18S-48	Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.	564.99
Mountain-500 Silver, 52	BK-M18S-52	Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.	564.99
Mountain-500 Black, 40	BK-M18B-40	Suitable for any type of riding, on or off-road. Fits any budget. Smooth-shifting with a comfortable ride.	539