3

I'd like to get a 'combined' ranking of OR phrases with CONTAINSTABLE, but have not had any luck. Basically I'm seeking functionality for an AND/OR ability.

For example say I have a query such such as:

SELECT TOP 100 * 
FROM JobData AS FT_TBL 
INNER JOIN CONTAINSTABLE(JobData, Description, '"microsoft" OR "C#" OR "5 years" OR "winforms"') AS KEY_TBL 
    ON FT_TBL.Id = KEY_TBL.[KEY] 
ORDER BY KEY_TBL.[RANK] DESC

And my table has 1000s of job descriptions where some contain all keywords. What I am seeing is that it is giving the highest rank to those containing "microsoft" (or whatever the first term is). I'd like to have a combined ranking for the OR terms... Essentially a sum of the ranks of each phrase.

How can I accomplish this? It seems as though it should be rather trivial... but my searches have yielded no useful information on the matter.

gofr1
  • 15,741
  • 11
  • 42
  • 52
Skonen
  • 621
  • 5
  • 5

2 Answers2

3

I've found the answer to this after reading more technical details regarding the abilities of CONTAINSTABLE. My original assumption was that any OR terms should have been given equal weights in the list of terms, therefore meaning the total rank was the sum of each terms ranking... I of course found the results conflicted with this assumption, and that is why I asked the original question.

The solution to this problem is to weight each term equally using ISABOUT, which results in the equivalent behavior of summing the ranks of each term.

So for the example given in the original question, I can use the following to reach the desired results:

SELECT TOP 100 * FROM JobData AS FT_TBL INNER JOIN CONTAINSTABLE(JobData, Description, 'ISABOUT("Microsoft" weight(.25), "C#" weight(.25), "5 years" weight(.25), "winforms" weight(.25))') AS KEY_TBL ON FT_TBL.Id = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC

This can of course then be AND'ed with other terms for more complex queries.

Hope this can help someone else out if they find themselves with a similar problem to that which caused me to ask the original question.

Skonen
  • 621
  • 5
  • 5
0

I think perhaps this question and answer could answer your question here. how to write containstable searchcondition with and's and or's

Community
  • 1
  • 1
Drauka
  • 1,217
  • 2
  • 10
  • 21