Естественная (человеческая альфа-цифровая) сортировка в Microsoft SQL 2005

Ответ 1

Большинство решений на базе SQL я видел разрыв, когда данные становятся достаточно сложными (например, более одного или двух чисел в нем). Сначала я попытался реализовать функцию NaturalSort в T-SQL, которая соответствовала моим требованиям (среди прочего, обрабатывала произвольное количество чисел внутри строки), но производительность была слишком медленной.

В конечном счете, я написал скалярную функцию 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.Read)]
    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, запустите следующие команды в Query Analyzer:

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", следуйте инструкциям здесь, чтобы включить его. Перед тем, как это сделать, убедитесь, что вы учитываете последствия для безопасности. Если вы не администратор db, убедитесь, что вы обсуждаете это с вашим администратором, прежде чем вносить какие-либо изменения в конфигурацию сервера.

Примечание2. Этот код неправильно поддерживает интернационализацию (например, предполагает, что десятичный маркер ".", не оптимизирован для скорости и т.д. Предложения по его улучшению приветствуются!

Изменить: Переименована функция "Натурализовать" вместо "NaturalSort", поскольку она не выполняет никакой фактической сортировки.

Ответ 2

order by LEN(value), value

Не идеально, но хорошо работает во многих случаях.

Ответ 3

Я знаю, что это старый вопрос, но я просто наткнулся на него, и поскольку он не получил принятого ответа.

Я всегда использовал способы, подобные этому:

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

RedFilter 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

и в коде С#:

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

Ответ 5

Я знаю, что в этот момент это немного устарело, но в поисках лучшего решения я столкнулся с этим вопросом. В настоящее время я использую функцию для заказа. Он отлично работает для моих целей сортировки записей, которые называются смешанными альфа-цифрами ( "элемент 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)

Он легко утроит время обработки для простого чтения данных, поэтому оно не может быть идеальным решением.

Ответ 6

Здесь представлено решение, написанное для 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 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

Ответ 7

Вот другое решение, которое мне нравится: http://www.dreamchain.com/sql-and-alpha-numeric-sort-order/

Это не Microsoft SQL, но поскольку я оказался здесь, когда искал решение для Postgres, я думал, что добавление этого здесь поможет другим.

Ответ 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)

Ответ 9

Если у вас возникли проблемы с загрузкой данных из БД для сортировки на С#, то я уверен, что вы будете разочарованы любым подходом при программном программировании в БД. Когда сервер собирается сортировать, он должен вычислить "воспринимаемый" порядок так же, как и каждый раз.

Я бы предложил добавить дополнительный столбец для хранения предварительно обработанной сортируемой строки, используя некоторый метод С#, когда данные сначала вставлены. Вы можете попытаться преобразовать числа в диапазоны фиксированной ширины, например, так что "xyz1" превратится в "xyz00000001". Затем вы можете использовать обычную сортировку SQL Server.

Из-за того, что я хочу использовать собственный рожок, я написал статью CodeProject, в которой была реализована проблема, поставленная в статье CodingHorror. Не стесняйтесь украсть из моего кода.

Ответ 10

Вы можете использовать следующий код для решения проблемы:

Select *, 
    substring(Cote,1,len(Cote) - Len(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1)))alpha,
    CAST(RIGHT(Cote, LEN(Cote) - PATINDEX('%[0-9]%', Cote)+1) AS INT)intv 
FROM Documents 
   left outer join Sites ON Sites.IDSite = Documents.IDSite 
Order BY alpha, intv

С уважением, [email protected]

Ответ 11

Я только что прочитал статью о такой теме. Ключевым моментом является то, что вам нужно только целочисленное значение для сортировки данных, а строка "rec" принадлежит пользовательскому интерфейсу. Вы можете разделить информацию в двух полях, например, альфа и num, отсортировать по альфа и num (отдельно), а затем показать строку, составленную с помощью альфа + num. Вы можете использовать вычисляемый столбец для создания строки или представления. Надеюсь, что это поможет.

Ответ 12

Просто отсортируйте по

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

 ##

Ответ 13

Я все еще не понимаю (возможно, из-за моего плохого английского).

Вы можете попробовать:

ROW_NUMBER() OVER (ORDER BY dbo.human_sort(field_name) ASC)

Но он не будет работать для миллионов записей.

Вот почему я предложил использовать триггер, который заполняет отдельный столбец с человеческим значением.

При этом:

• встроенные функции T-SQL действительно медленно, и Microsoft предлагает использовать Вместо этого функции .NET.
• Значение человека постоянное, поэтому нет смысла вычислять его каждый раз когда выполняется запрос.