使用Chinese_Hong_Kong_Stroke_90_CI_AS排序规则时SQL Server中的模糊匹配

Question

假设我们按如下方式创建一个表：

create table my_table (
id int, 
city nvarchar(256) collate Chinese_Hong_Kong_Stroke_90_CI_AS)

INSERT INTO my_table (id, city)
VALUES (1, 'Shanghai');

INSERT INTO my_table (id, city)
VALUES (2, 'Shandong');

INSERT INTO my_table (id, city)
VALUES (3, 'Shanxi');

INSERT INTO my_table (id, city)
VALUES (4, 'Shaanxi');

现在，my_table中有四条记录：

id  city
1   Shanghai
2   Shandong
3   Shanxi
4   Shaanxi

以下SQL查询返回相同的数字。如何避免这个错误？

select top 1 id from my_table order by DIFFERENCE(city, 'Shanghai') desc
select top 1 id from my_table order by DIFFERENCE(city, 'Shandong') desc

另一个问题：

select top 1 id from my_table order by DIFFERENCE(city, 'Shannxi') desc 

当正确答案应该是4时，返回3。

Answer 1

该问题是由列的排序规则引起的。根据文档，SOUNDEX和DIFFERENCE依赖于排序规则。

一种可能的解决方案是：

select top 1 id
from my_table
order by DIFFERENCE(city collate SQL_Latin1_General_CP1_CI_AS, 'Shanghai') desc

select top 1 id
from my_table
order by DIFFERENCE(city collate SQL_Latin1_General_CP1_CI_AS, 'Shandong') desc

我想我会添加另一个列，其中包含一个存储与city完全相同的值的SQL_Latin1_General_CP1_CI_AS排序规则。

尽管你很有兴趣知道你到底想要完成什么？因为在您当前的排序规则中，这两个单词的发音显然完全相同。

这也值得一读Beyond SOUNDEX & DIFFERENCE