Full text serach on all words in any order

HI,
I have requirement where I need to do full text search on all the
search text. For example, for the search text "Black Helmet" , I should get
the following records having "Black Helmet" as well as "Helmet Black".
SQL server version we are using is :
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
My requirement is like this, from the access 97 form search i need to do
this which uses SQL server as database.
Please help on how it can be done.
Thanks,
Vani
Vani,
the correct Sql-Query for "Black Helmet" would be:
SELECT * FROM [yourtable]
WHERE CONTAINS([yourindexedfield], ' "Black" NEAR "Helmet")
The NEAR clause searches within the next 50 words. You could use this
additional query to decrease the range
SELECT * FROM [yourtable]
WHERE CONTAINS([yourindexedfield], ' "Black" NEAR "Helmet")
AND (PATINDEX('%black%', (SUBSTRING([yourindexedfield], PATINDEX('%helmet%',
[yourindexedfield]) - 100, 200)))>0)
The additional Patindex clause searches only 100 letter before and after
'%helmet%'.
Regards, Gerald.
"Vani" wrote:

> HI,
> I have requirement where I need to do full text search on all the
> search text. For example, for the search text "Black Helmet" , I should get
> the following records having "Black Helmet" as well as "Helmet Black".
> SQL server version we are using is :
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> My requirement is like this, from the access 97 form search i need to do
> this which uses SQL server as database.
> Please help on how it can be done.
> Thanks,
> Vani
>
|||Or you could try
SELECT * FROM [yourtable]
WHERE CONTAINS([yourindexedfield], ' "Black Helmet" or "Helmet Black")
My tests reveal that this performs better than using the NEAR operator.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:12DAB737-AAA8-4F2F-84C7-4FD3BE0AEC0F@.microsoft.com...[vbcol=seagreen]
> Vani,
> the correct Sql-Query for "Black Helmet" would be:
> SELECT * FROM [yourtable]
> WHERE CONTAINS([yourindexedfield], ' "Black" NEAR "Helmet")
> The NEAR clause searches within the next 50 words. You could use this
> additional query to decrease the range
> SELECT * FROM [yourtable]
> WHERE CONTAINS([yourindexedfield], ' "Black" NEAR "Helmet")
> AND (PATINDEX('%black%', (SUBSTRING([yourindexedfield],
> PATINDEX('%helmet%',
> [yourindexedfield]) - 100, 200)))>0)
> The additional Patindex clause searches only 100 letter before and after
> '%helmet%'.
> Regards, Gerald.
> "Vani" wrote:
|||Thanks Gerald and Hilary for the early response.
I need to sort the serach results on the Rank basis that is based the very
exact result to approximate results.
How can i do this rankings?
How about using freetext search ie, freetext("Helmet black") ?
Thanks,
Vani
"Hilary Cotter" wrote:

> Or you could try
> SELECT * FROM [yourtable]
> WHERE CONTAINS([yourindexedfield], ' "Black Helmet" or "Helmet Black")
> My tests reveal that this performs better than using the NEAR operator.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:12DAB737-AAA8-4F2F-84C7-4FD3BE0AEC0F@.microsoft.com...
>
>
|||FreeText Rank will be skewed higher for exact matches. Distance and order
are also factored into the rank, so Black Helmet will be ranked highest
followed by Helmet Black, and then Black is my Helmet.
"Vani" <Vani@.discussions.microsoft.com> wrote in message
news:3B558847-4FEE-429F-9ED4-B9AD7E028BC6@.microsoft.com...[vbcol=seagreen]
> Thanks Gerald and Hilary for the early response.
> I need to sort the serach results on the Rank basis that is based the very
> exact result to approximate results.
> How can i do this rankings?
> How about using freetext search ie, freetext("Helmet black") ?
> Thanks,
> Vani
> "Hilary Cotter" wrote: