ranking full text search results of several strings

Question

I am getting some strings from a frontend and I would like to find all entries in a full text indexed view that match as many of these strings as possible. Example strings would be:

Bla di bladi

This can be translated into this query:

SELECT 
    *
FROM [Schema].[SomeFullTextIndexedView] 
WHERE CONTAINS (*, '"*bla*" OR "*di*" OR "*bladi*"')

which works fine. Let us say that for the sake of argument the query returns these results:

Column1 Column2 Column3
bla  rte     
bla di   xxx
bladi    tttytyt     
bla di  bladi

What I would also like to do is introduce some kind of rank where rank is larger the more strings are matched. The rank is then used to sort the results descendigly:

Column1 Column2 Column3 Rank
bla di  bladi   3
bla di   xxx    2
bla  rte        1
bladi    tttytyt     

1

Is there anything in full text search that I could exploit for this? Thanks.

It is being a long time, but just in case of others are searching for this. You can't rank data with `CONTAINS` and `FREETEXT`. Actually, you should use `CONTAINSTABLE` or `CONTAINSTABLE`, look at [Limit Search Results with RANK](https://learn.microsoft.com/en-us/sql/relational-databases/search/limit-search-results-with-rank?view=sql-server-ver15) — Guokas, Mar 13 '21 at 09:37

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

In common, you can use FREETEXTTABLE that returns RANK column:

SELECT 
    *
FROM [Schema].[SomeFullTextIndexedView]  AS t 
INNER JOIN FREETEXTTABLE([Schema].[SomeFullTextIndexedView] , *, '"*bla*" OR "*di*" OR "*bladi*"') as k ON t.Id = k.[key]
ORDER BY k.[RANK] DESC

But in your example text strings are not relevant and it always returns 0. Try it on real data.

edited May 23 '17 at 10:33

Community

1
1

answered Aug 22 '16 at 10:20

Backs

24,430
5
58
85

Note: `ORDER BY RANK` not `KEY` – gofr1 Aug 22 '16 at 12:18
this works great. just curious how would I implement a fuzzy search in this scenario? Let us say the search string provided is bal but the database only contains the strings bla and bladi. – cs0815 Aug 23 '16 at 13:09
1

@csetzkorn MSSQL Server fulltext search is suitable for simple text search. I worked with it about a year, and I can say, that if you want to do something more complex than simple CONTAINS or FREETEXTTABLE, it will become very difficult. If you need to build a complex text search system, use something like ElasticSearch. – Backs Aug 25 '16 at 02:13
you should be using CONTAINSTABLE https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/freetexttable-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word, or stopword, and will be discarded. – Michael B. May 06 '20 at 00:20

score 0 · Answer 2 · answered Aug 22 '16 at 12:15

You can use CONTAINSTABLE

The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria.

SELECT  *
FROM [Schema].[SomeFullTextIndexedView] t 
INNER JOIN CONTAINSTABLE([Schema].[SomeFullTextIndexedView], ColumnToSearch, '"*bla*" OR "*di*" OR "*bladi*"') c
    ON t.ID = c.[KEY]
ORDER BY [RANK] DESC

ranking full text search results of several strings

2 Answers2