Multi Column full-text search question

I would like to select rows containing some text which is more than one word.
The problem is that the text can appear in different colums, meaning, one
word in this column and another in that. moreove, I need to select only the
rows containing all the words.
The fulltext search is on one table only (multi columns)
The text size in words is variable
Can someone help?
Thanks in advance
This is a difficult question. Basically Contains will only see the words in
your search phrase if they occur in the same column, no matter what type of
search you are doing; single column, multiple column, or all columns.
For FreeText the hit will come if the word or one of the generations of the
word occurs in any of the columns.
create database multicolumn
go
use multicolumn
go
sp_fulltext_database 'enable'
go
create table fulltexttable
(pk int not null identity constraint fulltexttablepk primary key,
charcol1 char(30),
charcol2 char(30),
charcol3 char(30))
GO
insert into fulltexttable(charcol1, charcol2) values('james','bond')
insert into fulltexttable(charcol1, charcol2) values('james bond','mike')
GO
create fulltext catalog test as default
GO
create fulltext index on fulltexttable(charcol1, charcol2)
key index fulltexttablepk
GO
select * from containstable(fulltexttable,*,'"james bond"') -- 1 row, 2nd
one
select * from containstable(fulltexttable,charcol1,'"james bond"') -- 1 row,
2nd one
select * from containstable(fulltexttable,(charcol1,charcol2),'" james
bond"') -- 1 row, 2nd one
select * from freetexttable(fulltexttable,*,'james bond') -- 2 rows, 1 and 2
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dedi" <dedi@.discussions.microsoft.com> wrote in message
news:CFE85410-5AFF-4F84-B376-3E864DFDB623@.microsoft.com...
>I would like to select rows containing some text which is more than one
>word.
> The problem is that the text can appear in different colums, meaning, one
> word in this column and another in that. moreove, I need to select only
> the
> rows containing all the words.
> The fulltext search is on one table only (multi columns)
> The text size in words is variable
> Can someone help?
> Thanks in advance
sql