Column Names of Fulltext search result

I was trying to do full text search. I have no problem to do the search as
following:
select MemberID, Surname, Firstname from members where FREETEXT(*, N'moore')
It returned all rows for any column containing values that match the
meaning, but not the exact wording, of the text 'moore'.
My problem is that my client want to know the name(s) of the column(s) which
the keyword 'moore' was found. How can I get the required info?
And I also tried FREETEXTTABLE, which only returns a relevance ranking value
(RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
CONTAINSTABLE
Thanks in advance.
To get an exact search, ie moore, but not moores wrap your freetext search
in double quotes or use contains, ie
select MemberID, Surname, Firstname from members where FREETEXT(*,
N'"moore"')
You can't easily get the column where the hit occurs. You would have to do
something like this
create table members(MemberID int identity not null, surname varchar(20),
firstname varchar(20), constraint memberspk primary key (memberid))
GO
insert into members(surname, firstname) values('moore','moore')
insert into members(surname, firstname) values('moore','dave')
insert into members(surname, firstname) values('dave','moore')
insert into members(surname, firstname) values('dave','dave')
GO
sp_fulltext_database 'enable'
GO
create fulltext index on members(surname, firstname) key index memberspk
GO
select *, case
when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
'surname'
when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
'Firstname'
when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
'both'
else 'not sure'
end From members where contains(*,'moore')
Hilary Cotter
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
"X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>I was trying to do full text search. I have no problem to do the search as
> following:
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'moore')
> It returned all rows for any column containing values that match the
> meaning, but not the exact wording, of the text 'moore'.
> My problem is that my client want to know the name(s) of the column(s)
> which
> the keyword 'moore' was found. How can I get the required info?
> And I also tried FREETEXTTABLE, which only returns a relevance ranking
> value
> (RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
> CONTAINSTABLE
> Thanks in advance.
|||Hilary,
Thank you for your reply. I thought about this way too, but I have problem
with it. I had more than 50 columns to be searched, which means I have to
'when' more than 50 times, actually lots more than 50 times if I need the
column combinations, such as your 'both' case.
I was wondering if there is a straight forward way to do so. If no, I guess
I have to do some programming...
Thanks,
"Hilary Cotter" wrote:

> To get an exact search, ie moore, but not moores wrap your freetext search
> in double quotes or use contains, ie
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'"moore"')
> You can't easily get the column where the hit occurs. You would have to do
> something like this
> create table members(MemberID int identity not null, surname varchar(20),
> firstname varchar(20), constraint memberspk primary key (memberid))
> GO
> insert into members(surname, firstname) values('moore','moore')
> insert into members(surname, firstname) values('moore','dave')
> insert into members(surname, firstname) values('dave','moore')
> insert into members(surname, firstname) values('dave','dave')
> GO
> sp_fulltext_database 'enable'
> GO
> create fulltext index on members(surname, firstname) key index memberspk
> GO
> select *, case
> when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
> 'surname'
> when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
> 'Firstname'
> when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
> 'both'
> else 'not sure'
> end From members where contains(*,'moore')
>
> --
> Hilary Cotter
> 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
>
> "X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
> news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>
>
|||You could also limit your FT query to a certain column.
For example
select MemberID, Surname, Firstname from members where
contains(surname,'moore')
and do the same for every fulltext indexed column in your table and then
combine the results.
Thanks
"X. Zhang" wrote:
[vbcol=seagreen]
> Hilary,
> Thank you for your reply. I thought about this way too, but I have problem
> with it. I had more than 50 columns to be searched, which means I have to
> 'when' more than 50 times, actually lots more than 50 times if I need the
> column combinations, such as your 'both' case.
> I was wondering if there is a straight forward way to do so. If no, I guess
> I have to do some programming...
> Thanks,
> "Hilary Cotter" wrote: