如何找出排序规则是使用单词排序还是字符串排序? [英] How to find out whether collation uses word sort or string sort?

问题描述

https://stackoverflow.com/a/361059/14731 讨论了单词排序"和单词排序"之间的区别字符串排序".

https://stackoverflow.com/a/361059/14731 discusses the differences between "word sort" and "string sort".

当SQL排序规则将使用单词排序"还是字符串排序"时，如何以编程方式查询?

推论:所有归类是否对Unicode字符串使用单词排序"，对非Unicode字符串使用字符串排序"?

Corollary: Do all collations use "word sort" for Unicode strings and "string sort" for non-Unicode strings?

SELECT * from sys.fn_HelpCollations()
WHERE name = 'SQL_Latin1_General_CP1_CI_AS'

提供了许多有关排序规则的详细信息，但请注意，它没有提及单词排序".

provides a lot of details about the collation, but notice that it makes no mention of "word sort".

推荐答案

• srutzky的很好的答案表明，除了由SQL_整理器处理的非Unicode类型以外，其他所有数据根据"Unicode排序规则"进行排序.
• 令人困惑的是，Microsoft不使用Unicode标准的排序规则.

• 根据 https://support.microsoft.com/zh-我们/kb/322112

  • srutzky's excellent answer reveals that, with the exception of non-Unicode types processed by SQL_ collators, all other data is sorted according to "Unicode Collation" rules.
  • Confusingly, Microsoft does not use the Unicode standard's sorting rules.

  • According to https://support.microsoft.com/en-us/kb/322112

    SQL Server 2000支持两种排序规则:

    SQL Server 2000 supports two types of collations:

    • SQL排序规则
    • Windows排序规则

    [...]

    对于Windows归类，使用与Unicode数据相同的算法来实现非Unicode数据的比较.

    For a Windows collation, a comparison of non-Unicode data is implemented by using the same algorithm as Unicode data.

    [...]

    SQL排序规则对非Unicode数据进行排序与​​Microsoft Windows操作系统提供的任何排序例程都不兼容；但是，Unicode数据的排序与Windows排序规则的特定版本兼容.

    A SQL collation's rules for sorting non-Unicode data are incompatible with any sort routine that is provided by the Microsoft Windows operating system; however, the sorting of Unicode data is compatible with a particular version of the Windows sorting rules.

  • 我将其解释为:

  • I interpret this as meaning that:

    • SQL_归类器是"SQL归类"
    • 所有其他整理器都是"Windows整理器".
    • 除了由SQL_归类器处理的非Unicode类型之外，所有其他数据均根据"Windows归类"进行排序.
    • SQL_ collators are "SQL collations"
    • All other collators are "Windows collators".
    • With the exception of non-Unicode types processed by SQL_ collators, all other data is sorted according to "Windows collations".

    因此，让我们深入研究"Windows排序规则".

    So, let's dig into "Windows collations".

    • 根据 https://msdn. microsoft.com/en-us/library/ms143515(v=sql.105).aspx

    对于Unicode数据类型，数据比较基于Unicode代码点.

    For Unicode data types, data comparisons are based on the Unicode code points.

  • winnls.h 包含简要概述单词排序:
  • winnls.h contains a brief overview of "word sort":

  //  Sorting Flags.
  //
  //    WORD Sort:    culturally correct sort
  //                  hyphen and apostrophe are special cased
  //                  example: "coop" and "co-op" will sort together in a list
  //
  //                        co_op     <——-  underscore (symbol)
  //                        coat
  //                        comb
  //                        coop
  //                        co-op     <——-  hyphen (punctuation)
  //                        cork
  //                        went
  //                        were
  //                        we’re     <——-  apostrophe (punctuation)
  //
  //
  //    STRING Sort:  hyphen and apostrophe will sort with all other symbols
  //
  //                        co-op     <——-  hyphen (punctuation)
  //                        co_op     <——-  underscore (symbol)
  //                        coat
  //                        comb
  //                        coop
  //                        cork
  //                        we’re     <——-  apostrophe (punctuation)
  //                        went
  //                        were
  

  • 最后，根据 https://msdn.microsoft.com/zh-CN/library/windows/desktop/dd318144(v = vs.85).aspx

    [...]除连字符和撇号外，所有标点符号和其他非字母数字字符都位于任何字母数字字符之前.连字符和撇号与其他非字母数字字符的处理方式有所不同，以确保诸如"coop"和"co-op"之类的单词在排序列表中保持在一起.

    [...] all punctuation marks and other nonalphanumeric characters, except for the hyphen and the apostrophe, come before any alphanumeric character. The hyphen and the apostrophe are treated differently from the other nonalphanumeric characters to ensure that words such as "coop" and "co-op" stay together in a sorted list.

  这篇关于如何找出排序规则是使用单词排序还是字符串排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！