SYSK 9: Multiple column searches with one WHERE clause?
Yes, SQL 2005 can do that!
Here is an example (yes, it’s not very logical, but it gives you an idea on what you can do with the contains clause)…
USE AdventureWorks;
GO
SELECT Production.Product.ProductID, Name
FROM Production.Product
LEFT JOIN Production.ProductReview ON Production.Product.ProductID = Production.ProductReview.ProductID
WHERE CONTAINS((Production.Product.Name, Production.ProductReview.Comments), ' "Mountain" OR "Road" ')
GO
Here are some other cool search criteria:
• 'bike NEAR performance'
• '"chain*" OR "full*"' -- looking for ‘chain’ or ‘full’ being prefixes
• (Description, @SearchWord) -- variables
The only “gotcha” is that those columns must be full-text indexed…
Source: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/996c72fc-b1ab-4c96-bd12-946be9c18f84.htm
