Product name search Best practices

Hi,

I have enabled full-text search on ProductName column of my Products table,
using ProductId (PK) as key.

My asp.net web aoolication, filters the search (remove none alpha-numeric
and unwanted chars) and pass it as parameter to the stored procudure.
Following is a typical SP code that executes:



declare @_ProductName nvarchar(1024)
set @_ProductName = N' "24" NEAR "dell" NEAR "lcd" NEAR "monitor" NEAR
"with" NEAR "hight" NEAR "100mm" NEAR "adjustment" '

select productname
from Products
where contains (ProductName, @_ProductName)

However, it seems that "CONTAINS" method has some limitation on very
specific LONG search keywords. i.e. Above code will not return any rows,
but if i remove some parts of it like ("dell" NEAR "lcd" NEAR "monitor")
will return correct result.

On the other hnd, FREETEXT return too many rows. Does anyone has a better
method of searching a single full-text column, for product search that could
be used in an asp.net web application?


Kind Regards

Med

You would be best to use a containstable function and then order by
rank. The problem with your approach is that all the words must exist
in the row before it is returned. So if one is missing, the row will
not show up in a search.

On Jan 30, 9:43 am, "Med" wrote:
> Hi,
>
> I have enabled full-text search on ProductName column of my Products table,
> using ProductId (PK) as key.
>
> My asp.net web aoolication, filters the search (remove none alpha-numeric
> and unwanted chars) and pass it as parameter to the stored procudure.
> Following is a typical SP code that executes:
>
> declare @_ProductName nvarchar(1024)
> set @_ProductName = N' "24" NEAR "dell" NEAR "lcd" NEAR "monitor" NEAR
> "with" NEAR "hight" NEAR "100mm" NEAR "adjustment" '
>
> select productname
> from Products
> where contains (ProductName, @_ProductName)
>
> However, it seems that "CONTAINS" method has some limitation on very
> specific LONG search keywords.  i.e. Above code will not return any rows,
> but if i remove some parts of it like ("dell" NEAR "lcd" NEAR "monitor")
> will return correct result.
>
> On the other hnd, FREETEXT return too many rows.  Does anyone has a better
> method of searching a single full-text column, for product search that could
> be used in an asp.net web application?
>
> Kind Regards
>
> Med