Microsoft SQL 2005中的自然(人类字母数字)排序

我们有一个大型数据库,我们有数据库端分页.这很快,在几分之一秒内从数百万条记录中返回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 - 你的答案可能会有效,但是为所有这些文本值添加排序键确实感觉像是一个丑陋的妥协.

1> 小智..：

---

order by LEN(value), value

不完美,但在很多情况下效果很好.

---

如果数据是`rec10aa`,`rec14b`,则会中断.

---

借调@OrbMan,更糟糕的是它打破了'zzz`,'aaaa`

2> RedFilter..：

---

我看到的大多数基于SQL的解决方案在数据足够复杂时会中断(例如,其中包含多个或两个数字).起初我试图实现在T-SQL一个NaturalSort功能能够满足我的要求(除其他事项外,处理数字的字符串中的任意数字),但表现方式过于缓慢.

最后,我在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:此代码不能正确支持国际化(例如,假设小数标记为".",未针对速度进行优化等.欢迎提出改进建议!

编辑:将函数重命名为Naturalize而不是NaturalSort,因为它不进行任何实际排序.

---

很抱歉添加到旧线程,如果使用`[SqlFunction(DataAccess = DataAccessKind.None,IsDeterministic = true)]`,它将改善性能.由于SQL服务器优化的方式.

3> Seph..：

---

我知道这是一个老问题,但我刚刚遇到它,因为它没有得到一个公认的答案.

我总是使用类似的方式:

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

唯一常见的问题是,如果您的列不会转换为VARCHAR(MAX),或者LEN([Column])> 1000(但如果您愿意,可以将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地址进行排序,因为您现在正在进入非常具体的数字组合,而不是简单的文本+数字.

4> 小智..：

---

我知道现在这有点老了,但在寻找更好的解决方案时,我遇到了这个问题.我目前正在使用一个功能来订购.它适用于我的目的是排序以混合字母数字命名的记录('项目1','项目10','项目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)

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

5> plalx..：

---

这是为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

6> Roman Starko..：

---

对于索引不重要的合理大小的数据集,RedFilter的答案很有用,但是如果你想要一个索引,则需要进行几次调整.

首先,将该功能标记为不进行任何数据访问,并且具有确定性和精确性:

[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;

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

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');
    }
}