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?
TrialUserAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
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 CroweDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.