[thelist] Relevancy Algorithm for ASP

[Wade Armstrong]

on 3/5/03 2:52 PM, Rob Smith at rob.smith at THERMON.com wrote:

>> CONTAINS([column], 'FORMSOF (INFLECTIONAL, [input]) ')
>
> This really doesn't work to my desirability. Interesting Concept whoever
> developed it though.

> But want it to figure out:
>
> SELECT FT_TBL.ProductAuto, FT_TBL.ProductID, FT_TBL.ProductDesc,
> KEY_TBL.RANK
> FROM  dbo.Product FT_TBL INNER JOIN
> CONTAINSTABLE(Product, ProductID, '%15140%', 10) KEY_TBL ON _  <---<<
> FT_TBL.ProductAuto = KEY_TBL.[KEY]
> WHERE  CONTAINS(FT_TBL.ProductID, 'FORMSOF(INFLECTIONAL,"%15140%")') <---<<
> ORDER BY KEY_TBL.RANK DESC

Okay, I think I have a solution to this... from reading BOL, I think - but
I'm not positive - that the wildcard for full-text searching is *, not %.
I've honestly never tried this, but BOL gives the example:
CONTAINS(column, '"text*"') and notes that the double quotes (") inside the
single quotes (') will cause the asterisk (*) to be evaluated as a wildcard.

If that doesn't turn out to be correct, you may be able to make this work
with a small table design change... I think the first query you have  would
work if "B4X" and "15140" were in different columns, and instead of having
one ProductID field, you had a ClassID and ItemID field that together formed
a composite primary key. thus your query would be:
For example (is one I'm hashing out now)

SELECT FT_TBL.ProductAuto, FT_TBL.ProductID, FT_TBL.ProductDesc,
KEY_TBL.RANK
FROM         dbo.Product FT_TBL INNER JOIN
  CONTAINSTABLE(Product, ItemID, '15140', 10) KEY_TBL ON _
  FT_TBL.ProductAuto = KEY_TBL.[KEY]
WHERE  CONTAINS(FT_TBL.ItemID, '15140')
ORDER BY KEY_TBL.RANK DESC

Right? Am I understanding what you're trying to do correctly?

Wade