CODEWITHSUNDEEP
db2db2-luw
C C
C C

Reputation: 449

Using DB2 Text Search with ranked results on small VARCHAR column

(edit) DB2 10.5.0 on Linux

Hoping someone might have some insight. I am more or less forced to use DB2, so cannot bolt in something like elasticsearch, etc.

Say I have a simple table with single column COMPANY_NAME VARCHAR(240). Has db2ts running and a text search index on it.

I have company names like this:

MY GENERAL COMPANY
MY COMPANY
MY SUPER AWESOME COMPANY
MY COMPANY NAMED DUDE
MYLO COMPANIO

I execute query: 

SELECT COMPANY_NAME FROM COMPANY_TABLE WHERE CONTAINS('MY COMPANY~0.6') = 1

I can use SCORE to ORDER the results, but all SCORE does is count the number of times the word(s) appear in the text. So in this case SCORE will sort that last name to the bottom, but all other names are on equal par. Not helpful.

What I really want is an "affinity ranking" based on how CONTAINS decided to select the result set. Intuitively I would expect a human to accept this ranked list as an answer:

COMPANY_NAME
---------------------------
MY COMPANY
MY COMPANY NAMED DUDE
MY GENERAL COMPANY
MY SUPER AWESOME COMPANY
MYLO COMPANIO

I get the result set from CONTAINS as above, which is good. I just cannot figure out how to get the result set to ORDER BY "affinity to search terms"

Upvotes: 0

Views: 217

Answers (1)

Charles
Charles

Reputation: 23848

Just a guess...think you want to use SCORE()...it shouldn't be returning 1, it should return a value between 0 and 1.

SELECT 
   COMPANY_NAME
   , integer(score(COMPANY_NAME,'MY COMPANY~') * 100) as relevance
FROM COMPANY_TABLE 
WHERE CONTAINS(COMPANY_NAME,'MY COMPANY~0.6') = 1
order by relevance desc

Upvotes: 0

Related Questions