[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • 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 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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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