How do you implement a fulltext search over multiple columns in sql server?

Question

I am trying to implement a fulltext search on two columns which I created a view for: VendorName, ProductName. I have the full text index etc working but the actual query is what is causing some issues for me.

I want users to be able to use some standard search conventions, AND OR NOT and grouping of terms by () which is fine but I want to apply the search over both the columns so for example if I were to run a query such as:

SELECT * FROM vw_Search 
WHERE CONTAINS((VendorName, ProductName), "Apple AND iTunes")

It seems to be applying the query to each column individually i.e. checking vendor name for both terms and then checking product name for both terms which wont match unless the vendor was called "Apple iTunes".

If I change the query to :

SELECT * FROM vw_Search 
WHERE CONTAINS(VendorName, "Apple OR iTunes") 
AND CONTAINS(ProductName, "Apple OR iTunes")

then it works but breaks other search queries (such as searching for just apple) and from user writing the query it doesn't make much sense as what they are likely to write is AND but it requires an OR to work.

What I want is it to return if between the two the search term was valid so it would match all vendors named apple with a product name itunes for example.

Should I create a separate field in the view that concatenates the Vendor and Product fields and performs the first query on that new field or is there something I am missing out?

Aside from that would anyone know of an existing method of validating the queries?

Answer 1

In earlier versions of SQL Server, the queries matched across multiple columns.

However, this was considered a bug.

To match across multiple columns, you should concatenate them in a computed column and create an index over that column.