主页 / dba / 问题 / 126744
Accepted
db2
Asked: 2016-01-21 06:28:43 +0800 CST

ORDER BY 和比较混合的字母和数字字符串

  • 772

我们需要对通常是需要“自然”排序的数字和字母混合字符串的值进行一些报告。诸如“P7B18”或“P12B3”之类的东西。@字符串主要是字母序列，然后是数字交替。不过，这些片段的数量和每个片段的长度可能会有所不同。

我们希望这些数字部分按数字顺序排序。显然，如果我直接用 处理这些字符串值ORDER BY，那么“P12B3”将出现在“P7B18”之前，因为“P1”早于“P7”，但我希望反过来，因为“P7”自然在前面“P12”。

我还希望能够进行范围比较，例如@bin < 'P13S6'或类似的。我不必处理浮点数或负数；这些严格来说是我们正在处理的非负整数。字符串长度和段数可能是任意的，没有固定的上限。

在我们的例子中，字符串大小写并不重要，尽管如果有一种方法可以以一种可识别的方式执行此操作，其他人可能会发现这很有用。所有这一切中最丑陋的部分是我希望能够在WHERE子句中进行排序和范围过滤。

如果我在 C# 中执行此操作，这将是一项非常简单的任务：进行一些解析以将 alpha 与数字分开，实现 IComparable，然后您就基本上完成了。当然，SQL Server 似乎没有提供任何类似的功能，至少据我所知。

任何人都知道使这项工作的任何好技巧？是否有一些鲜为人知的能力来创建实现 IComparable 并使其行为符合预期的自定义 CLR 类型？我也不反对 Stupid XML Tricks（另请参见：列表连接），并且我在服务器上也提供了 CLR 正则表达式匹配/提取/替换包装函数。

编辑： 作为一个更详细的例子，我希望数据表现得像这样。

SELECT bin FROM bins ORDER BY bin

bin
--------------------
M7R16L
P8RF6JJ
P16B5
PR7S19
PR7S19L
S2F3
S12F0

即将字符串分解为所有字母或所有数字的标记，并分别按字母或数字对它们进行排序，最左边的标记是最重要的排序项。就像我提到的，如果您实现 IComparable，在 .NET 中小菜一碟，但我不知道您如何（或是否）可以在 SQL Server 中执行此类操作。这肯定不是我在 10 年左右的工作中遇到过的事情。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    想要一种明智、有效的方法将字符串中的数字排序为实际数字吗？考虑为我的 Microsoft Connect 建议投票：支持“自然排序”/DIGITSASNUMBERS 作为排序选项

    没有简单的内置方法可以做到这一点，但有一种可能性：

    通过将字符串重新格式化为固定长度的段来规范化字符串：

    • 创建一个类型为 的排序列VARCHAR(50) COLLATE Latin1_General_100_BIN2。50 的最大长度可能需要根据段的最大数量及其潜在的最大长度进行调整。
    • 虽然规范化可以在应用程序层更有效地完成，但使用 T-SQL UDF 在数据库中处理此问题将允许将标量 UDF 放入AFTER [or FOR] INSERT, UPDATE触发器中，这样您就可以保证正确设置所有记录的值，即使是那些通过即席查询等进入。当然，该标量 UDF 也可以通过 SQLCLR 处理，但需要对其进行测试以确定哪个实际上更有效。**
    • UDF（无论是在 T-SQL 还是 SQLCLR 中）应该：
      • 通过读取每个字符并在类型从字母切换到数字或从数字切换到字母时停止来处理未知数量的段。
      • 对于每个段，它应该返回一个固定长度的字符串，该字符串设置为任何段的最大可能字符/数字（或者可能 max + 1 或 2 以考虑未来的增长）。
      • Alpha 段应左对齐并用空格右填充。
      • 数字段应右对齐并左填充零。
      • 如果字母字符可以混合大小写，但排序需要不区分大小写，则将该UPPER()函数应用于所有段的最终结果（这样只需执行一次，而不是每个段）。考虑到排序列的二进制排序规则，这将允许正确排序。
    • AFTER INSERT, UPDATE在调用 UDF 设置排序列的表上创建触发器。为了提高性能，使用UPDATE()函数判断这个code列是否偶数在语句的SET子句中UPDATE（RETURN如果为false），然后在code列上加入伪表INSERTED和DELETED伪表，只处理code值发生变化的行. 请务必指定COLLATE Latin1_General_100_BIN2该 JOIN 条件，以确保确定是否有更改的准确性。
    • 在新的排序列上创建一个索引。

    例子：

    P7B18   -> "P     000007B     000018"
P12B3   -> "P     000012B     000003"
P12B3C8 -> "P     000012B     000003C     000008"

    在这种方法中，您可以通过以下方式进行排序：

    ORDER BY tbl.SortColumn

    您可以通过以下方式进行范围过滤：

    WHERE tbl.SortColumn BETWEEN dbo.MyUDF('P7B18') AND dbo.MyUDF('P12B3')

    或者：

    DECLARE @RangeStart VARCHAR(50),
        @RangeEnd VARCHAR(50);
SELECT @RangeStart = dbo.MyUDF('P7B18'),
       @RangeEnd = dbo.MyUDF('P12B3');

WHERE tbl.SortColumn BETWEEN @RangeStart AND @RangeEnd

    ORDER BY和过滤器都WHERE应该使用SortColumn由于排序规则而定义的二进制排序规则。

    仍然会在原始值列上进行相等比较。

    其他想法：

    • 使用 SQLCLR UDT。这可能会奏效，但与上述方法相比，它是否提供净收益尚不清楚。

      是的，SQLCLR UDT 可以使用自定义算法覆盖其比较运算符。这可以处理将值与已经是相同自定义类型的另一个值或需要隐式转换的值进行比较的情况。这应该处理条件中的范围过滤器WHERE。

      关于将 UDT 排序为常规列类型（不是计算列），这只有在 UDT 是“字节排序”时才有可能。“字节排序”意味着 UDT 的二进制表示（可以在 UDT 中定义）自然地以适当的顺序排序。假设二进制表示的处理方式与上述 VARCHAR(50) 列的方法类似，该列具有填充的固定长度段，则符合条件。或者，如果不容易确保二进制表示自然地以正确的方式排序，您可以公开 UDT 的方法或属性，该方法或属性输出一个正确排序的值，然后PERSISTED在其上创建一个计算列方法或属性。该方法需要是确定性的并标记为IsDeterministic = true。

      这种方法的好处是：

      • 不需要“原始值”字段。
      • 无需调用 UDF 来插入数据或比较值。假设ParseUDT 的方法接受P7B18值并转换它，那么您应该能够简单地将值自然地插入为P7B18. 并且使用 UDT 中设置的隐式转换方法，WHERE 条件还允许简单地使用 P7B18`。

      这种方法的后果是：

      • 如果使用按字节排序的 UDT 作为列数据类型，只需选择该字段即可返回二进制表示。或者，如果PERSISTED在 UDT 的属性或方法上使用计算列，那么您将获得该属性或方法返回的表示。如果您想要原始P7B18值，则需要调用编码为返回该表示的 UDT 的方法或属性。由于无论如何您都必须重写该ToString方法，因此这是提供此方法的不错选择。

      • 目前还不清楚（至少现在对我来说，因为我没有测试这部分）对二进制表示进行任何更改会有多容易/困难。更改存储的、可排序的表示可能需要删除并重新添加该字段。此外，如果以任何一种方式使用，删除包含 UDT 的程序集都会失败，因此您需要确保程序集中除了此 UDT 之外没有其他任何内容。您可以ALTER ASSEMBLY替换定义，但有一些限制。

        另一方面，该VARCHAR()字段是与算法断开连接的数据，因此它只需要更新列。如果有数千万行（或更多），那么可以通过批处理方法完成。

    • 实现实际上允许进行这种字母数字排序的ICU库。虽然功能强大，但该库仅提供两种语言：C/C++ 和 Java。这意味着您可能需要进行一些调整以使其在 Visual C++ 中工作，或者使用IKVM将 Java 代码转换为 MSIL 的可能性很小。该站点上链接的一两个 .NET 辅助项目提供了可以在托管代码中访问的 COM 接口，但我相信它们已经有一段时间没有更新了，我也没有尝试过。最好的办法是在应用层中处理这个问题，目标是生成排序键。然后将排序键保存到新的排序列中。

      这可能不是最实用的方法。不过，有这样的能力存在，还是很酷的。我在以下答案中提供了一个更详细的示例：

      是否有排序规则按以下顺序对以下字符串进行排序 1,2,3,6,10,10A,10B,11？

      但是该问题中处理的模式要简单一些。有关显示此问题中处理的模式类型也适用的示例，请转到以下页面：

      ICU 整理演示

      在“设置”下，将“数字”选项设置为“开”，其他所有选项都应设置为“默认”。接下来，在“排序”按钮的右侧，取消选中“差异强度”选项并选中“排序键”选项。然后将“输入”文本区域中的项目列表替换为以下列表：

      P12B22
P7B18
P12B3
as456456hgjg6786867
P7Bb19
P7BA19
P7BB19
P007B18
P7Bb20
P7Bb19z23

      单击“排序”按钮。“输出”文本区域应显示以下内容：

      as456456hgjg6786867
    29 4D 0F 7A EA C8 37 35 3B 35 0F 84 17 A7 0F 93 90 , 0D , , 0D .
P7B18
    47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P007B18
    47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P7BA19
    47 0F 09 2B 29 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19
    47 0F 09 2B 2B 0F 15 , 09 , FD F2 , DC C5 DC 06 .
P7BB19
    47 0F 09 2B 2B 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19z23
    47 0F 09 2B 2B 0F 15 5B 0F 19 , 0B , FD F4 , DC C5 DC 08 .
P7Bb20
    47 0F 09 2B 2B 0F 16 , 09 , FD F2 , DC C5 DC 06 .
P12B3
    47 0F 0E 2B 0F 05 , 08 , FD F1 , DC C5 DC 05 .
P12B22
    47 0F 0E 2B 0F 18 , 08 , FD F1 , DC C5 DC 05 .

      请注意，排序键是多个字段的结构，以逗号分隔。每个字段都需要独立排序，如果需要在 SQL Server 中实现，则需要解决另一个小问题。

    **如果对使用用户定义函数的性能有任何顾虑，请注意建议的方法对它们的使用最少。事实上，存储标准化值的主要原因是避免为每个查询的每一行调用一个 UDF。在主要方法中，UDF 用于设置 的值，并且SortColumn仅通过触发器完成。选择值比插入和更新更常见，并且某些值永远不会更新。对于在子句中使用 for a range 过滤器的每个查询，每个 range_start 和 range_end 值只需要一次 UDF 即可获得标准化值；UDF 不称为每行。INSERTUPDATESELECTSortColumnWHERE

    对于 UDT，其用法实际上与标量 UDF 相同。意思是，插入和更新将每行调用一次规范化方法来设置值。然后，规范化方法将在范围过滤器中的每个 range_start 和 range_value 的每个查询中调用一次，但不是每行。

    支持完全在 SQLCLR UDF 中处理规范化的一点是，鉴于它没有进行任何数据访问并且是确定性的，如果它被标记为IsDeterministic = true，那么它可以参与并行计划（这可能有助于INSERTandUPDATE操作），而T-SQL UDF 将阻止使用并行计划。

    • 9

相关问题