Firstly, thanks to Rob for another great book which picks up where the 'Beginning' version left off. It is a great learning resource and then an excellent reference.
I have been using Chapter 21 on Full-Text Search quite extensively. I am using 'CONTAINSTABLE' on a single table (well, actually a view but I don't think that makes any difference) which has two Full-Text-Indexed columns.
I am using a Stored Procedure for parsing search-terms, which can be found here: http://www.sqlservercentral.com/colu...ingroutine.asp
It converts a search term such as 'apple fruit' into:
"apple fruit" OR ("apple" NEAR "fruit") OR ("apple*" NEAR "fruit*") OR (FORMSOF(INFLECTIONAL,"apple") AND FORMSOF(INFLECTIONAL,"fruit"))
This search string works very well with CONTAINSTABLE, as long as both search terms are in the same column.
However, if the two search terms are in different columns, it is not so successful.
For example, suppose we have two full-text-indexed columns in the same table, called 'FoodName' and 'FoodType'.
There is a record in the table with 'Foodname' of 'Apple' and 'FoodType' of 'Fruit'.
The search string above will not find this record, because the 'NEAR' function only seems to work on terms within the same field, and does not seem to work across fields in different columns of the same record.
Is there any way of using 'NEAR' across more than one column? I would imagine it is a fairly common requirement but I can't think of any straightforward way to do it. I could create a trigger which updates an additional 'Search' field that is a concatenation of 'Foodname' and 'Foodtype' ('Apple Fruit', for example) and then do Full-Text-Index searches on this. But I'm sure there is a better way to do it.
Any thoughts much appreciated.