Simple Search result ranking algorithm for ASP / SQL Server

Hi all,

I am developing a website that enables visitors to search through a database of books - the search will query the author, title and keyword field.  As it is search mutiple fields, it would be useful to be able to rank the results by 'relevance' as do some search engines.

Does anyone know where I can get some good sample code for doing this?

Thanks

Bob
LVL 2
BobFettAsked:
Who is Participating?
 
AlfaNoMoreCommented:
SQL Server offers FREETEXT and CONTAINS methods. check them out in BOL (the help file), and see if they're of any use? MIght make your search terms more complicated, but does provided a relevance ranking already.
0
 
AlfaNoMoreCommented:
You could use some of the many freetext searches provided within SQL. Each of these will provide a Rank or a Weight against the search. This would give you your relevance.
0
 
BobFettAuthor Commented:
Hi Alfa,

Thanks for your prompt response - not entirely sure what you mean?  Could you expand on this please?  Are these 'free text' searches inherent in SQL or do you mean SQL Server?

Bob
0
 
thefritterfatboyCommented:
You'd have to set up a "Full Text Catalog".

Enable Full Text:

IF (SELECT DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
      EXEC dbo.sp_fulltext_database N'enable'

Setup a new catalog:

EXEC dbo.sp_fulltext_table N'new_full_text_table_name', N'create', N'your_exisiting_table_name_here', N'your_existing_table_primary_key'

Add columns from your table:

EXEC dbo.sp_fulltext_column N'new_full_text_table_name', N'column_name_from_existing_table', N'add', 0

Activate your FTC:

EXEC dbo.sp_fulltext_table N'new_full_text_table_name', N'activate'

You can then use searches by using the CONTAINSTABLE command to join a table onto your query which will return a column called rank. You will need to do some research into this but this bit of code will help you get started.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.