Solved

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

Posted on 2014-07-21
4
276 Views
Last Modified: 2014-08-04
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
Comment
Question by:TrialUser
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 40209543
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
 

Author Comment

by:TrialUser
ID: 40209645
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40209794
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40218471
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now