SQL Sorting with multiple attributes (order by conformity)

Question

I've got a table with certain attributes such as name and address. In a search field I want to let users of a system type in whatever they know about these people and return the results in a table. Obviously I do want the results that are fitting best to be on top.

1. Should the ranking be done in the SQL command or are there other ways that might work better?
2. Does anyone know a good way to order/rank the results based by highest conformity? I basically do want the person to show up on top that fits the search criteria most.

You can see an example here: http://sqlfiddle.com/#!2/19e9ad

Thanks for your help!

(I do use SQL Server currently, but any help is appreciated.)

Answer 1

You might want to check the full text indexes and searches - they have options for ranking that might do the job for you:

• Fulltext index creation info

... and searching functionality info:

• FREETEXTTABLE
• CONTAINSTABLE

Alternatively you can also work on your own solution like parsing the user input and matching word-by-word, then boosting results that match more words, exact words ordering etc. But you will need to handle a number of cases yourself (like multi-word cities like 'New York' etc.).