Business Systems Analyst - Clearing - SQL Server - ASP - VB (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

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

Read Other SQL Guru Questions

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article