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

order by LEN(value), value

Not perfect, but works well in a lot of cases.

Most of the SQL-based solutions I have seen break when the data gets complex enough (e.g. more than one or two numbers in it). Initially I tried implementing a NaturalSort function in T-SQL that met my requirements (among other things, handles an arbitrary number of numbers within the string), but the performance was way too slow.

Ultimately, I wrote a scalar CLR function in C# to allow for a natural sort, and even with unoptimized code the performance calling it from SQL Server is blindingly fast. It has the following characteristics:

• will sort the first 1,000 characters or so correctly (easily modified in code or made into a parameter)
• properly sorts decimals, so 123.333 comes before 123.45
• because of above, will likely NOT sort things like IP addresses correctly; if you wish different behaviour, modify the code
• supports sorting a string with an arbitrary number of numbers within it
• will correctly sort numbers up to 25 digits long (easily modified in code or made into a parameter)

The code is here:

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

To register this so that you can call it from SQL Server, run the following commands in 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

Then, you can use it like so:

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

Note: If you get an error in SQL Server along the lines of Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option., follow the instructions here to enable it. Make sure you consider the security implications before doing so. If you are not the db admin, make sure you discuss this with your admin before making any changes to the server configuration.

Note2: This code does not properly support internationalization (e.g., assumes the decimal marker is ".", is not optimized for speed, etc. Suggestions on improving it are welcome!

Edit: Renamed the function to Naturalize instead of NaturalSort, since it does not do any actual sorting.

I know this is a bit old at this point, but in my search for a better solution, I came across this question. I'm currently using a function to order by. It works fine for my purpose of sorting records which are named with mixed alpha numeric ('item 1', 'item 10', 'item 2', etc)

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

Then call

select item_name from my_table order by fnMixSort(item_name)

It easily triples the processing time for a simple data read, so it may not be the perfect solution.

If you're having trouble loading the data from the DB to sort in C#, then I'm sure you'll be disappointed with any approach at doing it programmatically in the DB. When the server is going to sort, it's got to calculate the "perceived" order just as you would have -- every time.

I'd suggest that you add an additional column to store the preprocessed sortable string, using some C# method, when the data is first inserted. You might try to convert the numerics into fixed-width ranges, for example, so "xyz1" would turn into "xyz00000001". Then you could use normal SQL Server sorting.

At the risk of tooting my own horn, I wrote a CodeProject article implementing the problem as posed in the CodingHorror article. Feel free to steal from my code.

You can use the following code to resolve the 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

regards, rabihkahaleh@hotmail.com

I've just read a article somewhere about such a topic. The key point is: you only need the integer value to sort data, while the 'rec' string belongs to the UI. You could split the information in two fields, say alpha and num, sort by alpha and num (separately) and then showing a string composed by alpha + num. You could use a computed column to compose the string, or a view. Hope it helps

I'm fashionably late to the party as usual. Nevertheless, here is my attempt at an answer that seems to work well (I would say that). It assumes text with digits at the end, like in the original example data.

First a function that won't end up winning a "pretty SQL" competition anytime soon.

CREATE FUNCTION udfAlphaNumericSortHelper (
@string varchar(max)
)
RETURNS @results TABLE (
    txt varchar(max),
    num float
)
AS
BEGIN

  DECLARE @txt varchar(max) = @string
  DECLARE @numStr varchar(max) = ''
  DECLARE @num float = 0
  DECLARE @lastChar varchar(1) = ''

  set @lastChar = RIGHT(@txt, 1)
  WHILE @lastChar <> '' and @lastChar is not null
  BEGIN 
    IF ISNUMERIC(@lastChar) = 1
    BEGIN 
        set @numStr = @lastChar + @numStr
        set @txt = Substring(@txt, 0, len(@txt))
        set @lastChar = RIGHT(@txt, 1)
    END
    ELSE
    BEGIN 
        set @lastChar = null
    END
  END
  SET @num = CAST(@numStr as float)

  INSERT INTO @results select @txt, @num
  RETURN;
END

Then call it like below:

declare @str nvarchar(250) = 'sox,fox,jen1,Jen0,jen15,jen02,jen0004,fox00,rec1,rec10,jen3,rec14,rec2,rec20,rec3,rec4,zip1,zip1.32,zip1.33,zip1.3,TT0001,TT01,TT002'


SELECT tbl.value  --, sorter.txt, sorter.num
FROM STRING_SPLIT(@str, ',') as tbl
CROSS APPLY dbo.udfAlphaNumericSortHelper(value) as sorter
ORDER BY sorter.txt, sorter.num, len(tbl.value)

With results: fox fox00 Jen0 jen1 jen02 jen3 jen0004 jen15 rec1 rec2 rec3 rec4 rec10 rec14 rec20 sox TT01 TT0001 TT002 zip1 zip1.3 zip1.32 zip1.33

I still don't understand (probably because of my poor English).

You could try:

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

But it won't work for millions of records.

That why I suggested to use trigger which fills separate column with human value.

Moreover:

• built-in T-SQL functions are really slow and Microsoft suggest to use .NET functions instead.
• human value is constant so there is no point calculating it each time when query runs.