Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Simple Search result ranking algorithm for ASP / SQL Server

Posted on 2005-04-15
Medium Priority
Last Modified: 2012-08-13
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?


Question by:BobFett
  • 2

Expert Comment

ID: 13790610
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.

Author Comment

ID: 13790658
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?


Accepted Solution

AlfaNoMore earned 500 total points
ID: 13790766
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.
LVL 10

Assisted Solution

thefritterfatboy earned 500 total points
ID: 13790891
You'd have to set up a "Full Text Catalog".

Enable Full Text:

      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.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question