Alternative scoring for CONTAINS operator?
539198Oct 12 2006 — edited Oct 14 2006I am trying to figure out how to query using CONTAINS and get slightly different result set scores than the default is returning. By default, it looks like rows are given the same score as long as they contain the same number of the search terms. For example, given the following table:
ID TEXT
-------------------
1 Eyes Wide Open
2 Open Your Eyes
3 My Eyes Are Wide Open
4 Eyes Open
5 Open Eyes
If I select based on a CONTAINS of "eyes open", all of the rows are returned with the same weight (which I think I understand based on the description of the default scoring algorithm).
But what I would like is a scoring algorithm that takes into account the number of words matched vs. the total number of words in the column, and preferrably also considers the order of the words in the column to the order they were searched on.
So given the previous table, if I select based on a CONTAINS of "eyes open", I would expect the scores to rank ID 4 the highest, followed by 5, 1, 2, 3. (In this case all of the column words are significant and I am using an empty stoplist).
Does anyone know how to accomplish this scoring behavior? Do I need to implement a new scoring algorithm, or can this be accomplished using a syntax for CONTAINS that I just don't know about?
Thanks for your help in advance.
-Chris