问题描述
我们有一个位置搜索页面，这给了我们一个我以前从未遇到过的挑战.
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...
我们在名为Black River Falls, WI"的城市有两家分店，最近我们在River Falls, WI"开设了一家.
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
显然，我们的查询使用LIKE"子句来匹配城市，但是当客户搜索文本River Falls"时，在搜索结果中，显示的第一个结果始终是Black River Falls".
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)
我知道我可以简单地更改排序顺序，让River Falls"先出现，但这是一个草率的解决方案，仅适用于这种情况.
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.
我想知道是否有办法通过 T-SQL (SQL Server 2008r2) 按最佳匹配"排序，如果我们搜索River Falls, WI"，River Falls"将获胜"如果我们搜索Black River Falls"WI，Black River Falls"就会起作用.
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.
推荐答案
您可以使用DIFFERENCE" 函数使用最接近的 SOUNDEX 匹配进行搜索.
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
来自 MSDN 文档:
From the MSDN Documentation:
返回的整数是 SOUNDEX 值中的字符数那是一样的.返回值范围从 0 到 4:0表示弱相似或无相似，4 表示强相似或相同的值.
DIFFERENCE 和 SOUNDEX 对排序规则敏感.
DIFFERENCE and SOUNDEX are collation sensitive.
这篇关于SQL查询按最接近的匹配排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！