SQL 2000 Full Text Search with Custom Thesaurus

Hi there,
I am creating a stored procedure to help my users find products in our
webshop. When they enter there search term, they can search for ALL the
words, ANY of the words, or the EXACT search term.

This is what I have so far:

CREATE PROCEDURE [dbo].[advancedSearch]
@search nvarchar(500) = NULL,
@p int = 1
AS
BEGIN

DECLARE @term nvarchar(500)

SET NOCOUNT ON;

-- @p = 0 means ANY words
-- @p = 1 means ALL words
-- @p = 2 means EXACT match

IF @p = 0 -- ANY
BEGIN
SELECT @term = '"' + @search + '"'

SELECT @term
SELECT R.RANK, I.prd_item, I.prd_wdesc, I.prd_wnotes
FROM tblProducts2 AS I JOIN FREETEXTTABLE(tblProducts2,prd_wdesc,@term)
AS R ON I.prd_item = R.[KEY]
ORDER BY R.RANK DESC
END

IF @p = 1 -- ALL
BEGIN
SELECT @term = @search
SELECT @term = REPLACE(@search, ' ',' AND ')
SELECT @term = '"' + @term + '"'

SELECT @term
SELECT R.RANK, I.prd_item, I.prd_wdesc, I.prd_wnotes
FROM tblProducts2 AS I JOIN CONTAINSTABLE(tblProducts2,prd_wdesc,
@term) AS R ON I.prd_item = R.[KEY]
ORDER BY R.RANK DESC
END

IF @p = 2 -- EXACT
BEGIN
SELECT @term = '"' + @search + '"'

SELECT @term
SELECT R.RANK, I.prd_item, I.prd_wdesc, I.prd_wnotes
FROM tblProducts2 AS I JOIN CONTAINSTABLE(tblProducts2,prd_wdesc,
@term) AS R ON I.prd_item = R.[KEY]
ORDER BY R.RANK DESC
END
END

I want to use a custom thesaurus using terms that are specific to the
products we sell. For instance, someone may type in bulbs when they mean
lamps, or gel when they mean filter, or gaffer when they mean gaffa.

We record all searches that product no results, so we can see what
people are looking for and failing to find.

I have played with Hilary's method:
http://www.indexserverfaq.com/thesaurus.htm but have found that it
ignores the first word, or requires a space at the beginning.

I may be doing this completely wrong, so any advice would be really
welcome at this stage.

Thanks in advance!

Steve

Can you give me an example of how this fails for you?

On Jul 24, 11:26 am, Dooza <stev....TakeThisOut@SPAM.dooza.tv> wrote:
> Hi there,
> I am creating a stored procedure to help my users find products in our
> webshop. When they enter there search term, they can search for ALL the
> words, ANY of the words, or the EXACT search term.
>
> This is what I have so far:
>
> CREATE PROCEDURE [dbo].[advancedSearch]
>         @search nvarchar(500) = NULL,
>         @p int = 1
> AS
> BEGIN
>
> DECLARE @term nvarchar(500)
>
>         SET NOCOUNT ON;
>
> -- @p = 0 means ANY words
> -- @p = 1 means ALL words
> -- @p = 2 means EXACT match
>
> IF @p = 0 -- ANY
> BEGIN
>         SELECT @term = '"' + @search + '"'
>
>         SELECT @term
>         SELECT R.RANK, I.prd_item, I.prd_wdesc, I.prd_wnotes
>         FROM tblProducts2 AS I JOIN FREETEXTTABLE(tblProducts2,prd_wdesc,@term)
> AS R ON I.prd_item = R.[KEY]
>         ORDER BY R.RANK DESC
> END    
>
> IF @p = 1 -- ALL
> BEGIN
>         SELECT @term = @search
>         SELECT @term = REPLACE(@search, ' ',' AND ')
>         SELECT @term = '"' + @term + '"'
>
>         SELECT @term
>         SELECT R.RANK, I.prd_item, I.prd_wdesc, I.prd_wnotes
>         FROM tblProducts2 AS I JOIN CONTAINSTABLE(tblProducts2,prd_wdesc,
> @term) AS R ON I.prd_item = R.[KEY]
>         ORDER BY R.RANK DESC
> END
>
> IF @p = 2 -- EXACT
> BEGIN
>         SELECT @term = '"' + @search + '"'
>
>         SELECT @term
>         SELECT R.RANK, I.prd_item, I.prd_wdesc, I.prd_wnotes
>         FROM tblProducts2 AS I JOIN CONTAINSTABLE(tblProducts2,prd_wdesc,
> @term) AS R ON I.prd_item = R.[KEY]
>         ORDER BY R.RANK DESC
> END
> END
>
> I want to use a custom thesaurus using terms that are specific to the
> products we sell. For instance, someone may type in bulbs when they mean
> lamps, or gel when they mean filter, or gaffer when they mean gaffa.
>
> We record all searches that product no results, so we can see what
> people are looking for and failing to find.
>
> I have played with Hilary's method:http://www.indexserverfaq.com/thesaurus.htmbut have found that it
> ignores the first word, or requires a space at the beginning.
>
> I may be doing this completely wrong, so any advice would be really
> welcome at this stage.
>
> Thanks in advance!
>
> Steve