• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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
0
MikeMCSD
Asked:
MikeMCSD
  • 8
  • 5
1 Solution
 
Anthony PerkinsCommented:
First of all there is no ranking order with FREETEXT.  Rank is only supported with FREETEXTTABLE and CONTAINSTABLE

Second, if you only want to return items with "Blue Baby Wrist Bracelet", make the phrase is enclosed in double quotes, as in:

Set @Words = '"Blue Baby Wrist Bracelet"'
0
 
MikeMCSDAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
>>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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeMCSDAuthor Commented:
"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?
0
 
Anthony PerkinsCommented:
>>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>
0
 
MikeMCSDAuthor Commented:
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".
0
 
Anthony PerkinsCommented:
>>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*"')
0
 
Anthony PerkinsCommented:
I should have clarified that both of those queries return:
CategoryID       CategoryName          Description              
1                     Beverages                Soft drinks, coffees, teas, beers, and ales
0
 
MikeMCSDAuthor Commented:
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.

0
 
Anthony PerkinsCommented:
>>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)
0
 
MikeMCSDAuthor Commented:
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.



0
 
Anthony PerkinsCommented:
>>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)
0
 
Anthony PerkinsCommented:
>>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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now