使用Collat​​ionInfo.Comparer在c#和SQL之间实现一致的排序 [英] Achieving consistent sorting between c# and SQL using CollationInfo.Comparer

问题描述

我正在尝试使用SMO的Collat​​ionInfo.Comparer来使我的C#代码像SQL Server那样进行排序.我已经获得正确的排序规则，但是我的项目仍然无法正确排序.

I am attempting to use CollationInfo.Comparer from SMO to get my c# code to sort like SQL Server. I have gotten the correct collation, but my items still do not sort correctly.

var collationInfo = CollationInfo.Collations.Single(x => x.Name == "SQL_Latin1_General_CP1_CS_AS") as CollationInfo;
var comparer = collationInfo.Comparer;

int c = comparer.Compare("Tri-Valley L", "Trimble L");

在这种情况下，c返回"1"，表明Tri-Valley L将在Trimble之后出现.

In this case c returns '1' indicating that Tri-Valley L will come after Trimble.

但是此代码在SQL Server中

However this code in SQL Server

DECLARE @T TABLE
(
    Name VARCHAR(20)
)

INSERT INTO @T
(
    Name
)
VALUES('Tri-Valley L'),
    ('Trimble L')

SELECT
Name
FROM
@T
ORDER BY Name

在Trimble之前返回Tri-Valley.

Returns Tri-Valley before Trimble.

排序规则是否只是比较不正确，还是我做错了什么?

Does the collation compare stuff just not work correctly, or am I doing something wrong?

推荐答案

旧版"SQL"排序规则排序与Windows"word-sort"算法不匹配.您需要对数据库中的列使用Windows排序规则(例如Latin1_General_CS_AS)，以获得相同的行为.

The legacy "SQL" collation sorting is not aligned with Windows "word-sort" algorithm. You'll need to use a Windows collation (e.g. Latin1_General_CS_AS) for the columns in the database to get the same behavior.

这篇关于使用Collat​​ionInfo.Comparer在c#和SQL之间实现一致的排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！