SQL Server using PATINDEX() and LEFT()
When searching names there are some assumptions we can make (based on first and last name being in separate columns):
A match in the last name is more important than a match in the first name
The position of the match within the last name is important: an earlier match is a better match
The first name should still be search
If present, a middle name is least important
It is possible to do this with SQL Server using the following proprietary extensions:
PATINDEX(needle, haystack): returns position of needle within haystack and (unfortunately in our use case) 0 if not present.
LEFT(string, count): returns substring of string up to length of count (note: will truncate string if length greater than count!)
SELECT TOP 10 firstName + ‘ ‘ + middleName + ‘ ‘ + lastName FROM Member WHERE [firstName] + ‘ ‘ + [middleName] + ‘ ‘ + [lastName] LIKE @query ORDER BY PATINDEX ( @query, LEFT([lastName] + ‘ ‘, 90) + LEFT([firstName] + ‘ ‘, 90) + [middleName] ), [lastName], [firstName], [middleName] – Note: the ‘ …. ‘ above is a string of spaces of length 90
We are making a big assumption: none of the name fields will have a length > 90. You may need to adjust this value for your use case. The reason we need to do this is that PATINDEX() will return 0 if the value is not present so we can’t simply due a nice ORDERBY PATINDEX(@query, lastName), PATINDEX(@query, firstName), PATINDEX(@query, middleName). Instead, we have to concatenate the name fields into one long string but pad them so that variable length of the names will not affect the rank they are put in.