SQL Server Fulltext - multiple columns

Question

Hi All,

I have what I think is a very basic scenario, but what I've read makes it sound like this is not easy in SQL Server Fulltext.

I have 2 columns, First and Last name. I want to support fulltext search on them such that if someone types "John Smith" people with a match on both first and last come up first.

Problem is, although it's easy to create an index across multiple columns, and easy to search multiple columns, the scoring doesn't reflect multiple columns.

SELECT [Key], Rank FROM CONTAINSTABLE([User], (FirstName,LastName), '<CLAUSE_HERE>')

1. If CLAUSE_HERE is "john smith" I get no results, because that phrase does not exist in either field.
2. If it's "john OR smith" I get all users with either name in either field, sorted in an unhelpful order.
3. If it's "john AND smith" I get no results, because neither field contains both words.

Seems like the only solution is to autogenerate a query that runs containstable on each field, does some math, sums the scores, etc. Does that sound right? Is there an easier way around it? My actual query has a lot more fields to it - this is a simplified example.

Thanks!

Answer 1

Make a computed column which mushes together the fields that you're interested in searching on (in a way that makes sense for your search formats), and full-text index that.

As far as I'm aware this is the only work around if you want to full-text in this way because of the behavior you describe in your question.

Answer 2

You would need to test it, but I wonder if you might be able to use the ISABOUT() function to apply a weight to each keyword. Your search clause might look something like:

ISABOUT(john weight(0.2), smith weight(0.8))