SQL Query sort by closest match

Question

We have a Locations search page that is giving us a challenge I've never run across before.

In our database, we have a list of cities, states, etc. with the corresponding geocodes. All was working fun until now...

We have two locations in a city named "Black River Falls, WI" and we've recently opened one in "River Falls, WI".

So our table has records as follows:

Location    City                State
-------------------------------------
1           Black River Falls   WI
2           Black River Falls   WI
3           River Falls         WI

Obviously our query uses a "LIKE" clause to match city, but when a customer searches the text "River Falls", in the search results, the first results shown are always "Black River Falls".

In our application, we always use the first match, and use it as the default. (We could change it, but it would be a lot of un-budgeted work)

I know I could simple change the sort order to have "River Falls" come up first, but that's a sloppy solution that works only in this one case.

What I'm wondering is if there is a way, through T-SQL (SQL Server 2008r2) to sort by "best match" where "River Falls" would "win" if we search for "River Falls, WI" and "Black River Falls" would work if we search for "Black River Falls" WI.

Answer 1

You can use the "DIFFERENCE" function to search using the closest SOUNDEX match.

Select * From Locations WHERE City=@City ORDER BY Difference(City, @City) DESC

From the MSDN Documentation:

The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.

DIFFERENCE and SOUNDEX are collation sensitive.