partial search using fulltext

Hello Friends,

I am using fulltext search in one of the project and the requirement is

I have to search a table basing on a string like 'Hot Choclolate' and store
the result set in to an XML string.

Second I have to do the partial search again with the same string but this
time I have to search with 'Hot' seperately and with 'Chocolate' seperately
and make another XML result set.

I have to combine these two (or may be three or may be more, depending on
the search string ex: 'Hot Spicy Chocolate' or 'Hot Brown Milky Chocolate'
etc.)

But the first 'Hot Chocolate' should take highiest weight and not the
splitted search with either 'Hot or 'Chocolate'

Is this possible to do this one query and make it look like simple or Do I
have to do this in different steps and concatinate all the results. The
problem is I don't have any control over how many words are coming through
the strings. It may come 'Hot Chocolate' or some time 'Hot Brown Milky
Chocolate' next time some 'blah blah.. n times'. I hope you understand what I
mean..

Please help me to solve this.

Thank you all.

Supremestar,

Perhaps something like this:

SELECT KeyColumn, SearchText
FROM dbo.Products AS FT_TBL
INNER JOIN CONTAINSTABLE(Products, SearchText,
'ISABOUT ("hot chocolate" weight (1.0), hot weight (.1),
chocolate weight (.1))' ) AS KEY_TBL
ON FT_TBL.KeyColumn = KEY_TBL.[Key]
ORDER BY KEY_TBL.RANK DESC;

The phrase "hot chocolate" is weighted as high as possible and each
individual word "hot", "chocolate" has a low weight. This should usually
give you the ordering that you are after, although a row that had "hot" in
it 10 times would bubble up very high.

Doing separate queries and merging the results is possible, but the rankings
of each query are independent of one another.

FWIW,
RLF


"Supremestar" wrote in message

> Hello Friends,
>
> I am using fulltext search in one of the project and the requirement is
>
> I have to search a table basing on a string like 'Hot Choclolate' and
> store
> the result set in to an XML string.
>
> Second I have to do the partial search again with the same string but this
> time I have to search with 'Hot' seperately and with 'Chocolate'
> seperately
> and make another XML result set.
>
> I have to combine these two (or may be three or may be more, depending on
> the search string ex: 'Hot Spicy Chocolate' or 'Hot Brown Milky Chocolate'
> etc.)
>
> But the first 'Hot Chocolate' should take highiest weight and not the
> splitted search with either 'Hot or 'Chocolate'
>
> Is this possible to do this one query and make it look like simple or Do I
> have to do this in different steps and concatinate all the results. The
> problem is I don't have any control over how many words are coming through
> the strings. It may come 'Hot Chocolate' or some time 'Hot Brown Milky
> Chocolate' next time some 'blah blah.. n times'. I hope you understand
> what I
> mean..
>
> Please help me to solve this.
>
> Thank you all.