full-text search in two columns

Question

Hi

Question about full-text search, i have set up a table with two collumns, title and body

I have added a full-text to include both columns.

Now i want to search "google"-style in both columns but i cant get a hit from both at the same querie.

The table include a row: title = abc123, body = xyz987


select title from table where contains((title,body), 'abc123 AND xyz987') is not working


select title from table where contains((title,body), 'abc123) is working and

select title from table where contains((title,body), 'xyz987') is working

How can i get FT to return hits from two different columns?

The columns is nvarchar(MAX) and the server is 2005-sp2

/looc

Answer 1

the search result must be in both columns for this to work, ie abc123 and xyz987 would have to be in title, or body, or in both columns.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Is this by design?
This makes FT-search totaly useless if cou include more then one column and want to use some sort of freetext-search.

/bjorn

Answer 3

yes, in earlier versions of SQL FTS Free Text could look across columns, but this was disabled in later versions.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

Sorry for the inconvenience.

This is a behavior that has been present in SQL 2005 and SQL 2008 of SQL Server. We search at column level, not at row/entity level. Thus, unless you have both search terms within one at least of the columns specified, we won't retrieve it as a result. We call this cross-column AND search, which is in our top 5 features we want to deliver in upcoming releases.
For now, the best workaround is to create a computed column that coalesces the content of your two columns, and create a FTIndex on top of that column instead. When searching for that column, you will then get the behavior expected. Indeed, it is not a free solution as requires some manageability effort, but no extra space or burdens should be needed for this to work.

Thanks,

Answer 5

Thank you for that reply.
I hope that feature will be in impemented soon.

anothe feature that would be nice with FT is a more common search language. To be able to write search-queries like lite-search or google wold be a nice help. It´s close now but i still have to know when write in a mmore special way.

/bjorn