Execute the following script in 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.99
