Hi,

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.

Right... it turns out that FREETEXTTABLE will search across columns whereas CONTAINSTABLE does not. To use the example previously, with FREETEXTTABLE a search on 'apple fruit' gives the record with both these words in it, even though they are each in different columns.
You do not need to use the 'NEAR' keyword (indeed, you cannot use it with FREETEXTTABLE).

Searches on 'apple' or 'fruit' give the same result.

However, with FREETEXTTABLE I am unable to use wildcards (i.e. '*').
A search on 'app' or 'fru' gives no records. Using the CONTAINSTABLE method above, it would result in the correct record.

All things considered, I think I will just use FREETEXTTABLE as on balance for this situation it solves a bigger problem than it creates.