微软SQL 2005中的自然排序（人类字母数字排序）

Question

我们有一个庞大的数据库，采用了数据库端分页技术。这种技术快速返回了50行页面数据，即使是从数百万条记录中获取，也只需要极短的时间。

用户可以定义他们自己的排序方式，基本上是选择希望以哪一列进行排序。这些列是动态的——有些列具有数字值，有些列具有日期，有些列具有文本。

尽管大多数排序都按预期效果排序，但文本排序的结果非常不如人意。我说它很不靠谱，虽然对于计算机来说是合理的，但会让用户感到沮丧。

例如，按字符串记录 ID 排序会得到类似以下内容的结果：

rec1
rec10
rec14
rec2
rec20
rec3
rec4

...等等。

我希望这能考虑到数字，所以：

rec1
rec2
rec3
rec4
rec10
rec14
rec20

我无法控制输入（否则我会只格式化前导000），也不能依赖于单一的格式 - 有些格式是像“{alpha code}-{dept code}-{rec id}”这样的东西。

我知道在C#中有几种方法可以做到这一点，但不能拉下所有记录来排序，因为那样太慢了。

有人知道如何在Sql server中快速应用自然排序吗？

---

我们正在使用：

ROW_NUMBER() over (order by {field name} asc)

然后我们会按照这个字段进行分页。

我们可以添加触发器，但我们不会这样做。他们所有的输入都是参数化的，但我不能改变格式 - 如果他们输入"rec2"和"rec10"，他们希望以这样的方式返回，并且按自然顺序排列。

---

我们有不同客户端使用不同格式的有效用户输入。

一个可能会是rec1、rec2、rec3、... rec100、rec101

而另一个则可能是：grp1rec1、grp1rec2、... grp20rec300、grp20rec301

当我说我们无法控制输入时，我的意思是我们不能强制用户更改这些标准 - 它们具有像grp1rec1这样的值，我无法将其重新格式化为grp01rec001，因为那样会改变用于查找和链接到外部系统的内容。

这些格式差异很大，但通常是字母和数字的混合。

在C#中对它们进行排序很容易 - 只需将其拆分为{"grp"，20，"rec"，301}，然后逐个比较序列值。

但是可能会有数百万条记录，而数据是分页的，我需要在SQL服务器上进行排序。

SQL服务器按值而不是比较进行排序 - 在C#中，我可以拆分值以进行比较，但在SQL中，我需要一些逻辑（非常快速地）获取一个一致排序的单个值。

@moebius - 你的答案可能有效，但它看起来像是一个丑陋的妥协，需要为所有这些文本值添加排序键。

- Keith

---

有一篇关于自然排序的Coding Horror文章。从评论中看来，SQL Server似乎没有这个功能。 - Corin Blaikie

这个问题有点老了，但我添加了一个基于CLR的解决方案，也许可以帮助其他人... - D'Arcy Rittich

虽然@RedFilter的答案以及Roman Starkov对其进行的改进都很好，但最佳解决方案是让SQL Server通过排序规则属性在内部处理此问题。这在操作系统中已经可以实现，例如在Windows 7中使用文件资源管理器按名称排序文件时。请投票支持我的Microsoft Connection建议，以便将此功能集成到SQL Server中，希望它能够真正实现： https://connect.microsoft.com/SQLServer/feedback/details/2932336/support-natural-sorting-digitsasnumbers-as-a-collation-option - Solomon Rutzky

微软已经在Azure反馈门户网站上表示，如果得到足够的投票支持，他们将为SQL Server添加自然排序的支持。 - Peter Aylett

就我个人而言，我尝试了一下，对结果还算满意。答案可能在这个页面最深处的某个地方，正在吞噬我的理智残骸。 - ShrapNull

Answer 1

order by LEN(value), value

并不完美，但在许多情况下都能很好地工作。

- randy

---

如果数据是 rec10aa，rec14b，那么这将会出错。 - D'Arcy Rittich

同意@OrbMan的观点，更糟糕的是它会破坏zzz和aaaa。 - Aidan Ryan

Answer 2

我看到的绝大多数基于SQL的解决方案在数据变得足够复杂时（例如其中包含了一个或两个以上的数字）会出现问题。起初，我尝试在T-SQL中实现一个自然排序函数以满足我的要求（其中包括处理字符串中任意数量的数字），但性能太慢了。

最终，我编写了一个用C#编写的标量CLR函数来实现自然排序，即使是未经优化的代码，从SQL Server调用它的性能也非常快速。它具有以下特点：

• 将正确地对前1000个字符或左右进行排序（可以在代码中轻松修改或制成参数）
• 正确地按小数进行排序，因此123.333排在123.45之前
• 由于上述原因，可能不会正确地对IP地址等内容进行排序；如果需要不同的行为，请修改代码
• 支持对包含任意数量数字的字符串进行排序
• 将正确地对长度最长为25位的数字进行排序（可以在代码中轻松修改或制成参数）

该函数的代码在这里：

using System;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

public class UDF
{
    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic=true)]
    public static SqlString Naturalize(string val)
    {
        if (String.IsNullOrEmpty(val))
            return val;

        while(val.Contains("  "))
            val = val.Replace("  ", " ");

        const int maxLength = 1000;
        const int padLength = 25;

        bool inNumber = false;
        bool isDecimal = false;
        int numStart = 0;
        int numLength = 0;
        int length = val.Length < maxLength ? val.Length : maxLength;

        //TODO: optimize this so that we exit for loop once sb.ToString() >= maxLength
        var sb = new StringBuilder();
        for (var i = 0; i < length; i++)
        {
            int charCode = (int)val[i];
            if (charCode >= 48 && charCode <= 57)
            {
                if (!inNumber)
                {
                    numStart = i;
                    numLength = 1;
                    inNumber = true;
                    continue;
                }
                numLength++;
                continue;
            }
            if (inNumber)
            {
                sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength));
                inNumber = false;
            }
            isDecimal = (charCode == 46);
            sb.Append(val[i]);
        }
        if (inNumber)
            sb.Append(PadNumber(val.Substring(numStart, numLength), isDecimal, padLength));

        var ret = sb.ToString();
        if (ret.Length > maxLength)
            return ret.Substring(0, maxLength);

        return ret;
    }

    static string PadNumber(string num, bool isDecimal, int padLength)
    {
        return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0');
    }
}

为了使其能从SQL Server中调用，您需要在查询分析器中运行以下命令进行注册：

CREATE ASSEMBLY SqlServerClr FROM 'SqlServerClr.dll' --put the full path to DLL here
go
CREATE FUNCTION Naturalize(@val as nvarchar(max)) RETURNS nvarchar(1000) 
EXTERNAL NAME SqlServerClr.UDF.Naturalize
go

然后，你可以这样使用它：

select *
from MyTable
order by dbo.Naturalize(MyTextField)

注意: 如果在 SQL Server 中出现类似于“禁用.NET Framework 中的用户代码执行。启用“clr enabled”配置选项。”的错误，请按照这里的说明进行启用。在这样做之前，请确保考虑安全性方面的影响。如果您不是数据库管理员，请确保在对服务器配置进行任何更改之前与管理员讨论。

注意2: 这段代码不支持国际化(例如，假定小数点是“.”，未经过速度优化等)，欢迎提出改进建议！

编辑: 将函数名称从 NaturalSort 改为 Naturalize，因为它并没有实际进行任何排序。

- D'Arcy Rittich

---

很抱歉打扰到了一个旧的帖子，如果您使用 [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic=true)] 替代原来的代码，这样可以提高性能。因为 SQL Server 的优化方式会受到影响。 - NoLifeKing

虽然“不是为速度优化的”，但在性能方面与我下面的答案相比如何？ - Seph

@Seph 我没有测试过，但我猜CLR方法会快得多。每次我将CLR字符串操作与本地SQL操作进行比较时，我发现它大约快了一个数量级。 - D'Arcy Rittich

非常好的答案，谢谢！在这上面创建一个索引有点棘手，请查看我的答案中的注释，如果您需要一个索引。 - Roman Starkov

+20 分给 @NoLifeKing 的建议（当然，这个回答也要 +1）。DataAccessKind 的默认值是 None，因此只需将其删除即可，但在此包括为 Read 会对性能产生明显的负面影响。同时，将 IsDeterministic=true 可以明显地提高性能。请参见我在问题上留下的评论（上面的链接）以支持我的建议，即将其作为 SQL Server 的排序选项内置到其中。谢谢！ - Solomon Rutzky

Answer 3

我知道这是一个老问题，但我刚刚遇到它，并且由于它没有被接受的答案，所以我想对它进行回答。

我一直使用类似于这样的方法：

SELECT [Column] FROM [Table]
ORDER BY RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))), 1000)

只有在你的列无法转换为VARCHAR（MAX），或者LEN（[Column]）> 1000（但如果需要，可以将其更改为其他值），才会出现问题。但是您可以使用这个大致的想法来满足您的需求。

此外，这比普通的ORDER BY [Column]的性能要差得多，但它确实可以给出OP所要求的结果。

编辑：仅为进一步澄清，如果您的列中有像1、1.15和1.5这样的小数值，那么以上内容将不起作用（它们将按{1, 1.5, 1.15}排序），因为这不是OP所要求的，但可以轻松地通过以下方法解决：

SELECT [Column] FROM [Table]
ORDER BY REPLACE(RIGHT(REPLICATE('0', 1000) + LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX)))) + REPLICATE('0', 100 - CHARINDEX('.', REVERSE(LTRIM(RTRIM(CAST([Column] AS VARCHAR(MAX))))), 1)), 1000), '.', '0')

结果：{1, 1.15, 1.5}

并且仍然完全在SQL内部。这将无法对IP地址进行排序，因为您现在涉及非常特定的数字组合，而不是简单的文本+数字。

- Seph

Answer 4

这是针对SQL 2000编写的解决方案。它可能适用于更新的SQL版本。

/**
 * Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
 *
 * @author Alexandre Potvin Latreille (plalx)
 * @param {nvarchar(4000)} string The formatted string.
 * @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
 * @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
 *
 * @return {nvarchar(4000)} A string for natural sorting.
 * Example of use: 
 * 
 *      SELECT Name FROM TableA ORDER BY Name
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                        ID  Name
 *  1.  A1.                         1.  A1-1.       
 *  2.  A1-1.                       2.  A1.
 *  3.  R1             -->          3.  R1
 *  4.  R11                         4.  R11
 *  5.  R2                          5.  R2
 *
 *  
 *  As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
 *  We can use this function to fix this.
 *
 *      SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
 *  TableA (unordered)              TableA (ordered)
 *  ------------                    ------------
 *  ID  Name                        ID  Name
 *  1.  A1.                         1.  A1.     
 *  2.  A1-1.                       2.  A1-1.
 *  3.  R1              -->         3.  R1
 *  4.  R11                         4.  R2
 *  5.  R2                          5.  R11
 */
ALTER FUNCTION [dbo].[udf_NaturalSortFormat](
    @string nvarchar(4000),
    @numberLength int = 10,
    @sameOrderChars char(50) = ''
)
RETURNS varchar(4000)
AS
BEGIN
    DECLARE @sortString varchar(4000),
        @numStartIndex int,
        @numEndIndex int,
        @padLength int,
        @totalPadLength int,
        @i int,
        @sameOrderCharsLen int;

    SELECT 
        @totalPadLength = 0,
        @string = RTRIM(LTRIM(@string)),
        @sortString = @string,
        @numStartIndex = PATINDEX('%[0-9]%', @string),
        @numEndIndex = 0,
        @i = 1,
        @sameOrderCharsLen = LEN(@sameOrderChars);

    -- Replace all char that have the same order by a space.
    WHILE (@i <= @sameOrderCharsLen)
    BEGIN
        SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' ');
        SET @i = @i + 1;
    END

    -- Pad numbers with zeros.
    WHILE (@numStartIndex <> 0)
    BEGIN
        SET @numStartIndex = @numStartIndex + @numEndIndex;
        SET @numEndIndex = @numStartIndex;

        WHILE(PATINDEX('[0-9]', SUBSTRING(@string, @numEndIndex, 1)) = 1)
        BEGIN
            SET @numEndIndex = @numEndIndex + 1;
        END

        SET @numEndIndex = @numEndIndex - 1;

        SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex);

        IF @padLength < 0
        BEGIN
            SET @padLength = 0;
        END

        SET @sortString = STUFF(
            @sortString,
            @numStartIndex + @totalPadLength,
            0,
            REPLICATE('0', @padLength)
        );

        SET @totalPadLength = @totalPadLength + @padLength;
        SET @numStartIndex = PATINDEX('%[0-9]%', RIGHT(@string, LEN(@string) - @numEndIndex));
    END

    RETURN @sortString;
END

- plalx

Answer 5

RedFilter's answer对于数据集大小合理且索引不关键的情况非常好，但是如果您想要一个索引，需要进行几个调整。

首先，将该函数标记为不执行任何数据访问并且是确定性和精确的：

[SqlFunction(DataAccess = DataAccessKind.None,
                          SystemDataAccess = SystemDataAccessKind.None,
                          IsDeterministic = true, IsPrecise = true)]

接下来，MSSQL对索引键大小有900字节的限制，因此如果自然化值是索引中唯一的值，则其长度必须最多为450个字符。如果索引包含多列，则返回值必须更小。需要进行两个更改：

CREATE FUNCTION Naturalize(@str AS nvarchar(max)) RETURNS nvarchar(450)
    EXTERNAL NAME ClrExtensions.Util.Naturalize

在 C# 代码中：

const int maxLength = 450;

最后，您需要向表中添加一个计算列，并且它必须是持久化的（因为MSSQL无法证明Naturalize是确定性和精确的），这意味着自然化值实际上存储在表中，但仍然自动维护：

ALTER TABLE YourTable ADD nameNaturalized AS dbo.Naturalize(name) PERSISTED

你现在可以创建索引了！

CREATE INDEX idx_YourTable_n ON YourTable (nameNaturalized)

我还对RedFilter的代码进行了一些更改：使用字符以增加清晰度，将重复空格删除并纳入主循环，一旦结果超过限制就退出，设置最大长度而不使用子字符串等。以下是结果：

using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;

public static class Util
{
    [SqlFunction(DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
    public static SqlString Naturalize(string str)
    {
        if (string.IsNullOrEmpty(str))
            return str;

        const int maxLength = 450;
        const int padLength = 15;

        bool isDecimal = false;
        bool wasSpace = false;
        int numStart = 0;
        int numLength = 0;

        var sb = new StringBuilder();
        for (var i = 0; i < str.Length; i++)
        {
            char c = str[i];
            if (c >= '0' && c <= '9')
            {
                if (numLength == 0)
                    numStart = i;
                numLength++;
            }
            else
            {
                if (numLength > 0)
                {
                    sb.Append(pad(str.Substring(numStart, numLength), isDecimal, padLength));
                    numLength = 0;
                }
                if (c != ' ' || !wasSpace)
                    sb.Append(c);
                isDecimal = c == '.';
                if (sb.Length > maxLength)
                    break;
            }
            wasSpace = c == ' ';
        }
        if (numLength > 0)
            sb.Append(pad(str.Substring(numStart, numLength), isDecimal, padLength));

        if (sb.Length > maxLength)
            sb.Length = maxLength;
        return sb.ToString();
    }

    private static string pad(string num, bool isDecimal, int padLength)
    {
        return isDecimal ? num.PadRight(padLength, '0') : num.PadLeft(padLength, '0');
    }
}

- Roman Starkov

---

对于@RedFilter的答案的这些改进，我给予+1。另外，请查看我在问题上留下的评论（上面），关于支持我的建议（在这里：https://dev59.com/hHVD5IYBdhLWcg3wRpaX#99vonYgBc1ULPQZFsU4O）将其作为SQL Server的排序选项内置。谢谢！ - Solomon Rutzky

我有点晚了，但这些是很棒的改进，谢谢！+1 - D'Arcy Rittich

Answer 6

我知道这个问题已经有点过时了，但在寻找更好的解决方案时，我遇到了这个问题。我目前正在使用一种按顺序排列的函数。对于我的目的，即对以混合字母数字命名的记录进行排序（'item 1'，'item 10'，'item 2'等），它可以很好地工作。

CREATE FUNCTION [dbo].[fnMixSort]
(
    @ColValue NVARCHAR(255)
)
RETURNS NVARCHAR(1000)
AS

BEGIN
    DECLARE @p1 NVARCHAR(255),
        @p2 NVARCHAR(255),
        @p3 NVARCHAR(255),
        @p4 NVARCHAR(255),
        @Index TINYINT

    IF @ColValue LIKE '[a-z]%'
        SELECT  @Index = PATINDEX('%[0-9]%', @ColValue),
            @p1 = LEFT(CASE WHEN @Index = 0 THEN @ColValue ELSE LEFT(@ColValue, @Index - 1) END + REPLICATE(' ', 255), 255),
            @ColValue = CASE WHEN @Index = 0 THEN '' ELSE SUBSTRING(@ColValue, @Index, 255) END
    ELSE
        SELECT  @p1 = REPLICATE(' ', 255)

    SELECT  @Index = PATINDEX('%[^0-9]%', @ColValue)

    IF @Index = 0
        SELECT  @p2 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255),
            @ColValue = ''
    ELSE
        SELECT  @p2 = RIGHT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
            @ColValue = SUBSTRING(@ColValue, @Index, 255)

    SELECT  @Index = PATINDEX('%[0-9,a-z]%', @ColValue)

    IF @Index = 0
        SELECT  @p3 = REPLICATE(' ', 255)
    ELSE
        SELECT  @p3 = LEFT(REPLICATE(' ', 255) + LEFT(@ColValue, @Index - 1), 255),
            @ColValue = SUBSTRING(@ColValue, @Index, 255)

    IF PATINDEX('%[^0-9]%', @ColValue) = 0
        SELECT  @p4 = RIGHT(REPLICATE(' ', 255) + @ColValue, 255)
    ELSE
        SELECT  @p4 = LEFT(@ColValue + REPLICATE(' ', 255), 255)

    RETURN  @p1 + @p2 + @p3 + @p4

END

然后调用。

select item_name from my_table order by fnMixSort(item_name)

它很容易使简单数据读取的处理时间增加三倍，因此可能不是完美的解决方案。

- JazzHands

Answer 7

这里还有一个我喜欢的解决方案：http://www.dreamchain.com/sql-and-alpha-numeric-sort-order/

虽然这不是针对 Microsoft SQL 的，但由于我在寻找 Postgres 的解决方案时最终来到了这里，所以我认为将其添加至此可以帮助其他人。

编辑：如果该链接失效，这里是代码。

CREATE or REPLACE FUNCTION pad_numbers(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace(($1 collate "C"),
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;

在postgresql中，“C”是默认的排序方式；如果你确定表格列不会被分配非确定性排序，则可以指定任何你想要的排序方式，或者删除排序语句。

用法：

SELECT * FROM wtf w 
  WHERE TRUE
  ORDER BY pad_numbers(w.my_alphanumeric_field)

- Simon

---

2021年，这个便宜而实用的解决方案仍然比我尝试过的其他方案更好地满足了我的需求。需要注意的是：如果您的列排序规则是不确定性的，则需要在函数中明确指定排序规则。 - Craig.Feied

Answer 8

以下是varchar数据的示例：

BR1
BR2
External Location
IR1
IR2
IR3
IR4
IR5
IR6
IR7
IR8
IR9
IR10
IR11
IR12
IR13
IR14
IR16
IR17
IR15
VCR

对我来说，这个方法最有效：

ORDER BY substring(fieldName, 1, 1), LEN(fieldName)

- Gut Feeling

Answer 9

只需按照排序

ORDER BY 
cast (substring(name,(PATINDEX('%[0-9]%',name)),len(name))as int)

 ##

- jack.mike.info

Answer 10

如果你在C#中从DB加载数据时遇到了麻烦，那么我相信你会对任何以编程方式在DB中进行操作都感到失望。当服务器进行排序时，它必须像你一样计算“感知”顺序--每次都要这样做。

我建议你添加一个额外的列来存储预处理的可排序字符串，使用一些C#方法，在数据第一次插入时进行处理。例如，你可以尝试将数字转换为固定宽度范围，这样“xyz1”就会变成“xyz00000001”。然后你就可以使用普通的SQL Server排序了。

冒昧自吹自擂，我写了一篇CodeProject文章，实现了CodingHorror文章中提出的问题。请随意借鉴我的代码。

- Chris Wuestefeld