SQL Server natural sort function

fnNaturalSort.sql

-- =================================================
-- Author:      Theodore Brown
-- Create date: 2016-12-02
-- Description: Sort alphanumeric strings naturally!
-- =================================================
CREATE FUNCTION [dbo].[fnNaturalSort]
(
    @string nvarchar(255)
)
RETURNS nvarchar(264)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @sortString nvarchar(264);
    DECLARE @startIndex int, @endIndex int;
    DECLARE @afterStartIndex nvarchar(255);
    DECLARE @firstNum varchar(10); -- max length of int

    SELECT @startIndex = PATINDEX('%[0-9]%', @string);
    SELECT @afterStartIndex = SUBSTRING(@string, @startIndex, LEN(@string));
    SELECT @endIndex = PATINDEX('%[^0-9]%', @afterStartIndex) - 1;

    SELECT @firstNum =
        CASE
            WHEN @endIndex < 0 THEN @afterStartIndex -- rest of string after start index is number
            ELSE SUBSTRING(@afterStartIndex, 1, @endIndex)
        END;

    SELECT @sortString =
        CASE
            WHEN LEN(@firstNum) = 0 THEN @string
            -- padd first number to 10 digits and replace it in the string
            ELSE STUFF(@string, @startIndex, LEN(@firstNum), REPLICATE('0', 10 - LEN(@firstNum)) + @firstNum)
        END;

    RETURN @sortString;
END

Hello theodorejb,
Thank you so much for this function. I really appreciate you sharing this.
Howeverm when I try to use it, I dont get the expected outcome I am hoping for.

My input string = '168LOAD, BOOK131, 17LOAD, 7BLACKJACK, Z11, Z10, AMZ, 78BLACK12, BOOK14, AMZ0'
When I run the below in SSMS, I get:
select * from dbo.NaturalSort('168LOAD, BOOK131, 17LOAD, 7BLACKJACK, Z11, Z10, AMZ, 78BLACK12, BOOK14, AMZ0')

Output:
SortableString
LOAD, BOOK

I was hoping I would get:
7BLACKJACK, 17LOAD, 78BLACK12, 168LOAD, AMZ, AMZ0, BOOK14, BOOK131, Z10, Z11

Can you please advise whenever free? Perhaps I am doing something wrong.

@martinjtlive The intention is to use the function in an ORDER BY clause. For example:

DECLARE @myStrings TABLE(
    value nvarchar(255)
);

INSERT INTO @myStrings (value)
VALUES ('168LOAD'), ('BOOK131'), ('17LOAD'),
    ('7BLACKJACK'), ('Z11'), ('Z10'), ('AMZ'),
    ('78BLACK12'), ('BOOK14'), ('AMZ0');

SELECT * FROM @myStrings
ORDER BY dbo.fnNaturalSort(value);

Output:

value
7BLACKJACK
17LOAD
78BLACK12
168LOAD
AMZ
AMZ0
BOOK14
BOOK131
Z10
Z11

GREAT function, thank you!!! Will you please provide guidance on how to pass DESC or ASC to the function?

Hi - I can't believe it was as easy as adding the direction to the ORDER BY clause - I thought it needed to be handled in the function. Works great - thank you again!

@glenn-pathe You're welcome! I'm glad you figured it out.