Natural (human alpha-numeric) sort in Microsoft SQL 2005

order by LEN(value), value

Wiem, że to stare pytanie, ale właśnie na nie trafiłem, ponieważ nie ma na nie zaakceptowanej odpowiedzi.

Zawsze używałem sposobów podobnych do tego:

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

Jedynymi częstymi czasami, kiedy to ma problemy, jest to, czy Twoja kolumna nie zostanie rzucona na VARCHAR (MAX), lub jeśli LEN ([Column]) > 1000 (ale możesz zmienić to 1000 na coś innego, jeśli chcesz), ale możesz użyć tego przybliżonego pomysłu do tego, czego potrzebujesz.

Również jest to znacznie gorsza wydajność niż normalne zamówienie przez [kolumnę], ale robi podaj wynik żądany w OP.

Edit: aby jeszcze bardziej wyjaśnić, powyższe nie zadziała, jeśli masz wartości dziesiętne, takie jak posiadanie 1, 1.15 i 1.5, (będą sortować jako {1, 1.5, 1.15}), ponieważ nie jest to to, o co prosi się w OP, ale można to łatwo zrobić przez:

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

Wynik: {1, 1.15, 1.5}

I nadal wszystko całkowicie w SQL. To nie posortuje adresów IP, ponieważ teraz dostajesz bardzo konkretne kombinacje liczb w przeciwieństwie do zwykłego tekstu + liczba.

Oto rozwiązanie napisane dla SQL 2000. Prawdopodobnie można go ulepszyć dla nowszych wersji 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

Wiem, że w tym momencie jest to trochę stare, ale w poszukiwaniu lepszego rozwiązania natknąłem się na to pytanie. Obecnie używam funkcji, aby zamówić przez. Działa dobrze w moim celu sortowania rekordów, które są nazwane mieszanymi liczbami alfanumerycznymi ('pozycja 1',' Pozycja 10',' Pozycja 2', itp.)

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

Następnie wywołaj

select item_name from my_table order by fnMixSort(item_name)

Łatwo potraja czas przetwarzania dla prostego odczytu danych, więc może nie być idealnym rozwiązaniem.

Odpowiedź RedFilter jest świetna dla zbiorów danych o rozsądnych rozmiarach, gdzie indeksowanie nie jest krytyczne, jednak jeśli chcesz mieć indeks, wymagane jest kilka poprawek.

Po pierwsze, zaznacz funkcję jako nie wykonującą dostępu do danych i będącą deterministyczną i precyzyjną:

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

Następnie MSSQL ma limit 900 bajtów na rozmiarze klucza indeksu, więc jeśli naturalizowana wartość jest jedyną wartością w indeksie, musi mieć maksymalnie 450 znaków. Jeśli indeks zawiera wiele kolumn, zwraca wartość musi być jeszcze mniejsza. Dwie zmiany:

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

Oraz w kodzie C#:

const int maxLength = 450;

Na koniec musisz dodać kolumnę obliczeniową do tabeli i musi ona zostać utrzymana (ponieważ MSSQL nie może udowodnić, że Naturalize jest deterministyczna i precyzyjna), co oznacza, że naturalizowana wartość jest faktycznie przechowywana w tabeli, ale nadal jest utrzymywana automatycznie:

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

Możesz teraz utworzyć indeks!

CREATE INDEX idx_YourTable_n ON YourTable (nameNaturalized)

Wprowadziłem też kilka zmian w kodzie Redfiltera: używając znaków dla jasności, włączenie usuwania duplikatów w pętli głównej, wyjście, gdy wynik jest dłuższy niż limit, ustawienie maksymalnej długości Bez podłańcucha itp. Oto wynik:

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

Oto inne rozwiązanie, które lubię: http://www.dreamchain.com/sql-and-alpha-numeric-sort-order/

To nie jest Microsoft SQL, ale ponieważ znalazłem się tutaj, gdy szukałem rozwiązania dla Postgres, pomyślałem, że dodanie tego tutaj pomoże innym.

Dla następujących danych varchar:

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

To działało najlepiej dla mnie:

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

Jeśli masz problem z załadowaniem danych z DB do sortowania w C#, to jestem pewien, że będziesz rozczarowany jakimkolwiek podejściem do robienia tego programowo w DB. Kiedy serwer ma sortować, musi obliczyć" postrzeganą " kolejność tak , jak ty byś to zrobił-za każdym razem.

Sugerowałbym dodanie dodatkowej kolumny do przechowywania wstępnie przetworzonego sortowalnego łańcucha, używając jakiejś metody C#, gdy dane są po raz pierwszy wstawiane. Możesz spróbować przekonwertować liczby na stałą szerokość zakresy, na przykład, więc "xyz1" zamieni się w "xyz00000001". Następnie możesz użyć normalnego sortowania SQL Server.

Z narażeniem własnego klaksonu, napisałem artykuł CodeProject implementujący problem przedstawiony w artykule CodingHorror. Zapraszam do wykraść z mojego kodu .

Właśnie czytałem gdzieś artykuł o takim temacie. Najważniejsze jest to, że do sortowania danych potrzebna jest tylko liczba całkowita, podczas gdy łańcuch 'rec' należy do interfejsu użytkownika. Można podzielić informacje na dwa pola, powiedzmy alpha i num, Sortuj według alpha i num (osobno), a następnie wyświetlając ciąg złożony przez alpha + num. Możesz użyć obliczonej kolumny do skomponowania ciągu znaków lub widoku. Hope it helps

Możesz użyć następującego kodu, aby rozwiązać problem:

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

Pozdrawiam, [email protected]

Po prostu Sortuj według

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

 ##

Nadal nie rozumiem (prawdopodobnie z powodu mojego kiepskiego angielskiego).

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

Dlatego zasugerowałem użycie triggera, który wypełnia oddzielna kolumna z wartością ludzką .

Ponadto:

• wbudowane funkcje T-SQL są naprawdę slow i Microsoft sugerują użycie Zamiast tego działa. NET.
• wartość ludzka jest stała, więc nie ma sensu jej obliczać za każdym razem gdy zapytanie działa.