How to order query results by search term first, and then alphabetically?

I'm using NHibernate to do a search on item name. I'm getting a paged list of items back from the database, and I'm ordering it by item name ascending.

So, if I search for 'term', I get back a page of results that contains 'term' anywhere in the result, and the page is ordered alphabetically.

For example, the results might look like this

a term
d term
g term
j term
p term
s term
term 1
term 2
v term    
z term

Technically this list is correct, because it's ordered alphabetically.

However, technically correct is not good enough for the client. They want the list to be ordered first by the relevance of the term being searched for, and then alphabetically.

So the result would become

term 1
term 2
a term
d term
g term
j term
p term
s term
v term    
z term

I don't know how to constru开发者_StackOverflow社区ct this query, or if its even possible. If this could be done in the application it would be much easier, but because of the paging, this has to be done on the database. This is what complicates things. Could somebody please point me in the right direction here?

---

I've never seen anything that sophisticated in SQL or Linq to search by 'relevancy'.

If this was my problem I would look to create a customised helper class which implements IComparer. You could then create any number of nuanced conditions in code. Of course this isnt done server side.

I wonder if there is a thrid party .net library, that could specifically quantify relevancy.

---

If it's possible to write a function that returns an integer based on the relevancy, you could do

SELECT MyField
FROM MyTable
WHERE MyField like '%term%'
ORDER BY GetRelevance(MyField, SearchTerm) DESC

Your function wouldn't have to be very complicated. It could just look to see if the Field Starts with the SearchTerm and return 2 and if it doesn't return 1. Then you could expand it if you have more criteria of relevancy.

Or if by relevancy you mean it starts with the search term, you could also split your DB query into two and union them together.

i.e.

SELECT MyField
FROM MyTable
WHERE MyField LIKE 'term%' 
ORDER by MyField

union
SELECT MyField
FROM MyTable
WHERE MyField LIKE '%term%' AND NOT LIKE 'term%'
ORDER BY MyField

It's not going to be good for your performance though.

---

Thanks to Ray's suggestion, I implement the following:

CREATE FUNCTION [dbo].[fnGetRelevance] 
(   
    @fieldName nvarchar(50),
    @searchTerm nvarchar(50)
)

RETURNS  int
AS
BEGIN
    if (@fieldName like @searchTerm + '%') -- starts with
    begin       
        return 0
    end
    else if ((@fieldName like '%' + @searchTerm + '%') and (@fieldName not like @searchTerm + '%')) -- contains, but doesn't start with 
    begin       
        return 1
    end

    return 1
END

GO

This resulted in the following performance impact for 1 of the larger queries: