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

Question

We have a large database on which we have DB side pagination. This is quick, returning a page of 50 rows from millions of records in a small fraction of a second.

Users can define their own sort, basically choosing what column to sort by. Columns are dynamic - some have numeric values, some dates and some text.

While most sort as expected text sorts in a dumb way. Well, I say dumb, it makes sense to computers, but frustrates users.

For instance, sorting by a string record id gives something like:

rec1
rec10
rec14
rec2
rec20
rec3
rec4

...and so on.

I want this to take account of the number, so:

rec1
rec2
rec3
rec4
rec10
rec14
rec20

I can't control the input (otherwise I'd just format in leading 000s) and I can't rely on a single format - some are things like "{alpha code}-{dept code}-{rec id}".

I know a few ways to do this in C#, but can't pull down all the records to sort them, as that would be to slow.

Does anyone know a way to quickly apply a natural sort in Sql server?

Answer 1

What exactly means that you cannot control the input, but can order in SQL server?

If you know how to do this in C# maybe the best thing is to write .NET function which returns some value ready to sort in human way (see OrbMan's answer for details).

Then call it in trigger on INSERT, UPDATE and store what C# function returns in separate column (or simply use persistent computed column) and order by this value.

Or use RANK_OVER() with C# function.

Answer 2

We're using:

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

And then we're paging by that.

We can add triggers, although we wouldn't. All their input is parametrised and the like, but I can't change the format - if they put in "rec2" and "rec10" they expect them to be returned just like that, and in natural order.

Answer 3

There is a Coding Horror article regarding natural sort. From the comments it seems this feature is not available in SQL Server.

Answer 4

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.

Answer 5

There's no human_sort in T-SQL, unfortunately. So I take it you're suggesting a C# function added to SQL.

Anyone know of a good function to use there? All the mechanisms I know (inc Jeff's from that post) compare two values, rather than return one value to sort conventionally.

Anyone know a better, T-SQL (or even better plain SQL:92 or 2003 standard) way to do this?

Answer 6

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.

Answer 7

We have valid user input that follows different formats for different clients.

One might go rec1, rec2, rec3, ... rec100, rec101

While another might go: grp1rec1, grp1rec2, ... grp20rec300, grp20rec301

When I say we can't control the input I mean that we can't force users to change these standards - they have a value like grp1rec1 and I can't reformat it as grp01rec001, as that would be changing something used for lookups and linking to external systems.

These formats vary a lot, but are often mixtures of letters and numbers.

Sorting these in C# is easy - just break it up into { "grp", 20, "rec", 301 } and then compare sequence values in turn.

However there may be millions of records and the data is paged, I need the sort to be done on the SQL server.

SQL server sorts by value, not comparison - in C# I can split the values out to compare, but in SQL I need some logic that (very quickly) gets a single value that consistently sorts.

@moebius - your answer might work, but it does feel like an ugly compromise to add a sort-key for all these text values.

Answer 8

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.

Answer 9

order by length(value) , value

Answer 10

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, [email protected]

Answer 11

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

Answer 12

ORDER BY LEN(value) , value

worked for me! thanks!

Answer 13

order by length(value) , value nice trick.

Answer 14

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

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.

Answer 15

I know this is an old question but I just came across it and since it's not got an accepted answer.

I have always used ways similar to this:

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

The only time this has issues is if your column won't cast to a VARCHAR(MAX), or if LEN([Column]) > 1000 (but you can change that 1000 to something else if you want), but you can use this rough idea for what you need.

Also this is much worse performance than normal ORDER BY [Column], but it does give you the result asked for in the OP