To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q010400-1.shtml

The SQL Guru Answers your Questions...


Today's question comes from Mike:

Hello Mr. Guru. Quick question.

How do I rank search results?

eg.
Search for "the dog"

Result
100% - the dog bla bla bla
50%  - a dog bla bla bla
25%  - and og bla bla bla

Is there a SQL function that does this or do I have to do it manually? If manually, how would I sort it?

Thanks!

There are a pair of sql functions that you may want to look at: SOUNDEX and DIFFERENCE. SOUNDEX phonetically encodes a string and returns the coded value. The more useful function, DIFFERENCE, phonetically compares two strings and returns a value from 0(not at all similar) to 4(very similar).

That's about in in pre SQL 7.0. Starting with SQL 7.0, I'd recommend using the full-text indexing features. You can do a full text search and returned ranked results, similar to what you have above. Look in SQL Server books online... there's more than enough there to get ya started.

Sean


Article Information
Article Title: SQL Guru: Ranking Search Results
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q010400-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers