Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

How to get the SQL free text search using contains query to return with ranking

I am doing a contains search on the columns on the database. I have already run separate queries to confirm text1, text2 and text3 are on the database. then I have the following query :

select * from Item I where contains(i.*,'text1') and contains(i.*,'text2') and contains(i.*,'text3')

how do I get the results with ranking from Sql server on the matches?
0
TrialUser
Asked:
TrialUser
  • 2
1 Solution
 
Brian CroweCommented:
use CONTAINSTABLE

http://msdn.microsoft.com/en-us/library/ms189760.aspx

I have found FREETEXTTABLE more useful in my applications as it performs pluralization and thesaurus checks as well.
0
 
TrialUserAuthor Commented:
Thanks for your help.
1) So containstable will not do pluralization and thesaurus check?
2) is containstable just the same as the contains query but with a ranking of the results?
0
 
Brian CroweCommented:
Yes to both,  With some tweaking the contains and containstable can do some intricate searching but for things like product and FAQ searches where I have applied it, I have found freetext/freetexttable a better fit.
0
 
Anthony PerkinsCommented:
how do I get the results with ranking from Sql server on the matches?
For obvious reasons, you cannot use CONTAINS.  You have to use CONTAINSTABLE instead.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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