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

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.