SQL Server带有不可打印字符的排序顺序 [英] SQL Server sort order with nonprintable characters

问题描述

我有一个标量值函数，该函数返回包含ASCII单位分隔符Char(31)的数据的varchar.我将此结果用作Order By子句的一部分，并尝试按升序排序.

I have a scalar value function that returns a varchar of data containing the ASCII unit seperator Char(31). I am using this result as part of an Order By clause and attempting to sort in ascending order.

我的标量值函数返回如下结果(拼写出不可打印的字符以供参考)

My scalar value function returns results like the following (nonprintable character spelled out for reference)

• ABC
• ABC(CHAR(31))DEF
• ABC(CHAR(31))DEF(CHAR(31))HIJ

我希望当我通过升序订购时，结果如下:

I would expect that when I order by ascending the results would be the following:

• ABC
• ABCDEF
• ABCDEFHIJ

相反，我看到的是完全相反的结果:

instead I am seeing the results as the complete opposite:

• ABCDEFHIJ
• ABCDEF
• ABC

现在我可以肯定这与不可打印字符有关，但是我不确定为什么.知道为什么会这样吗?

Now I am fairly certain that this has to do with the non-printable characters, but I am not sure why. Any idea as to why that is the case?

谢谢

推荐答案

您的

The sortorder can be influenced by your COLLATION settings. Following script, explicitly using Latin1_General_CI_AS as collation orders the items as you would expect.

;WITH q (Col) AS (
    SELECT 'ABC' UNION ALL
    SELECT 'ABC' + CHAR(31) + 'DEF' UNION ALL
    SELECT 'ABC' + CHAR(31) + 'DEF' + CHAR(31) + 'HIJ'
)
SELECT  *
FROM    q   
ORDER BY
        Col COLLATE Latin1_General_CI_AS

您正在使用什么排序规则?您可以使用以下方式验证您当前的数据库排序规则设置:

What collation are you using? You can verify your current database collation settings with

SELECT DATABASEPROPERTYEX('master', 'Collation') SQLCollation;

这篇关于SQL Server带有不可打印字符的排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！