Hi all,

Can anyone assist with search relevance?

I am trying to do a SQL search on a table from a ASP page which allows the user to either search by 'all words', 'any words' or 'exact phrase'. The user types in keyword(s) and each are then seperated by the space (with the exception of exact phrase search) and searched against the SQL table.

I am using charindex to search for specific words in small fields such as name. I have also switched on Ful Text Indexing for a larger field called Search (which is an amalgamation of various fields). For this field I use the CONTAINSTABLE and weighted search (ISABOUT)

At the end of the statement, I have 2 sums from charindex called NameScore and MetaScore and then the RANK from the CONTAINSTABLE search. The select statement is then put into a derived table to get me a total of all 3 scores and I order it by this.

OK, so I have the order of the highest scoring records, but I am at a loss how to do relevance. By this I mean 100%, 78% etc. Can anyone suggest any algorithms?

Looking forward to any replies. Thanks!