asked on

Full Text Search ranking order

I'm using Full Text Search like this:

WHERE FREETEXT(*, @Words)

Example: where @Words = "Blue Baby Wrist Bracelet"

I search the Name and Description columns of the table.
But when I do a search for the above example, which is in the product Name column,
that product does not come up first in the list. The products that show up first
are the ones with "Baby" in the Description column, ex:
"Baby Oval Stripe Top Baptismal Pin. 22KT Gold over Sterling Silver"
Then "Blue Baby Wrist Bracelet" shows up at 30 on the list.

Is there a way to get the search to list the items FIRST that have all the keywords
in the search. Like in the above example, I would want "Blue Baby Wrist Bracelet"
to be at the top of the search list.
thanks

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeMCSD

ASKER

This Search is going to be for a store with 6000 products. I think most people are just going to type in a few words and search. I want to make it so it's easy like that.
I will also offer an advanced search where they can "include all words".

But what I want to do is have the highest ranking items appear first, without having
the user to check a "include all words box". Does FREETEXTTABLE return the actual
items or just a ranking list? I'll give it a try now.

Anthony Perkins

>>Does FREETEXTTABLE return the actual items or just a ranking list? <<
It will return whatever you want it to return, by including them in your Select list. Also, understand the difference between the CONTAINS and the FREETEXT approach.

MikeMCSD

ASKER

"understand the difference between the CONTAINS and the FREETEXT approach"
I'm still confused about them.
Which one would be better for what I am doing?

Anthony Perkins

>>Which one would be better for what I am doing?<<
It really depends on your requirements, I have always used CONTAINS, as it has more options. But then I have never had the need to return data based on the meaning.

From BOL:
<quote>
CONTAINSTABLE
Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.

FREETEXTTABLE
Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, but not the exact wording, of the text in the specified freetext_string. FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement like a regular table name.

Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) for each row.

</quote>

MikeMCSD

ASKER

I had to use FREETEXTTABLE because when I search for "boys" I get all
the results. CONTAINSTABLE didn't show any, . . it only worked for "boy".
Same thing with "medal" and medals" . . . CONTAINSTABLE will only show
results for "medal".

Anthony Perkins

>>CONTAINSTABLE will only show results for "medal". <<
It really depends on how you called it. CONTAINS and CONTAINSTABLE are far more powerful. In order to return the plural you can use a "generation_term" with the INFLECTIONAL. Using the Categories table in the Northwind database, you can do something like this (I am using CONTAINS but you should get the idea):

This will return:
Select CategoryID, CategoryName, [Description]
From categories
Where CONTAINS(*, 'FORMSOF (INFLECTIONAL, drink)')

Another alternative is to use a prefix search like this:
Select CategoryID, CategoryName, [Description]
From categories
Where CONTAINS(*, 'FORMSOF (INFLECTIONAL, drink)')

But this last is not as effective, when it comes to words with "irregular" plurals.
WHERE CONTAINS(*, '"drink*"')

Anthony Perkins

I should have clarified that both of those queries return:
CategoryID CategoryName Description
1 Beverages Soft drinks, coffees, teas, beers, and ales

MikeMCSD

ASKER

The ranking order works good, but it returns everything . . I can't use "AND" with
FREETEXTTABLE . . I tried sending: "medals AND joseph"
A typical search might be: @Words = "medals joseph"
I break up the string into words before I call the stored procedure:

CREATE PROCEDURE SearchCat2
@Words varchar(50)
...........

SELECT P1.ProductID, P1.CategoryID, P1.prodNum, P1.Price, Category.DepartmentID
FROM Product AS P1
INNER JOIN Category ON P1.CategoryID = Category.CategoryID
INNER JOIN FREETEXTTABLE(Product,*, @Words ) AS P2
ON P1.ProductID = P2.[KEY]
ORDER BY P2.RANK DESC

I'm guessing I would need something like this:

CONTAINS(*, 'FORMSOF (INFLECTIONAL, medals)', 'FORMSOF (INFLECTIONAL, joseph)')
But I'm sending in a string . . I would need to break the words up I guess, but there
could be up to 5 words.

Anthony Perkins

>>I can't use "AND" with FREETEXTTABLE . . I tried sending: "medals AND joseph"<<
Right, FREETEXT does not support boolean logic, CONTAINS does.

>>I'm guessing I would need something like this:

CONTAINS(*, 'FORMSOF (INFLECTIONAL, medals)', 'FORMSOF (INFLECTIONAL, joseph)')<<

More like this (notice the singular medal):
CONTAINS(*, 'FORMSOF (INFLECTIONAL, medal, joseph)')<<

For example using the Categories table again:

Select CategoryID, CategoryName, [Description]
From Categories
Where CONTAINS(*, 'FORMSOF (INFLECTIONAL, drink, candy)')

Returns:
CategoryID CategoryName Description
3 Confections Desserts, candies, and sweet breads
1 Beverages Soft drinks, coffees, teas, beers, and ales

Notice how I am using "candy" and it recognizes "candies". (As an aside this could not be done with a prefix search)

MikeMCSD

ASKER

But can I send a string into this:
Where CONTAINS(*, 'FORMSOF (INFLECTIONAL, @Words)')

Do you know if this can be done too:

Where CONTAINSTABLE(*, 'FORMSOF (INFLECTIONAL, @Words)')

because the ranking feature brings the best results to the top.

Anthony Perkins

>>But can I send a string into this:
Where CONTAINS(*, 'FORMSOF (INFLECTIONAL, @Words)')<<
Nope. But there is nothing stopping you doing this:

Declare @ContainsCondition varchar(1000)

SET @ContainsCondition = 'FORMSOF (INFLECTIONAL, ' + @Words + ')'

Select CategoryID, CategoryName, [Description]
From categories
Where CONTAINS(*, @ContainsCondition)

Anthony Perkins

>>Do you know if this can be done too:

Where CONTAINSTABLE(*, 'FORMSOF (INFLECTIONAL, @Words)')

because the ranking feature brings the best results to the top.<<

Try it this way:

Declare @ContainsCondition varchar(1000)

SET @ContainsCondition = 'FORMSOF (INFLECTIONAL, ' + @Words + ')'

Select      c.[Description],
     c.CategoryName,
     c1.Rank
From      Categories c
      Inner Join CONTAINSTABLE (Categories, *, @ContainsCondition) c1 ON c.CategoryID = c1.[Key]
Where      c1.Rank > 2
Order By c1.Rank DESC