Natural sort in SQL Server

NaturalSort.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
 */
-- Original: https://stackoverflow.com/a/5583068/2672235
-- MySQL: https://stackoverflow.com/a/12257917/2672235

DROP FUNCTION IF EXISTS dbo.udf_FirstNumberPos;  
GO 
CREATE FUNCTION dbo.udf_FirstNumberPos (@instring nvarchar(4000))
RETURNS int
AS
BEGIN
	DECLARE @position int;
    DECLARE @tmp_position int;
    SET @position = 5000;
    SET @tmp_position = CHARINDEX('0', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position; 
    SET @tmp_position = CHARINDEX('1', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('2', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('3', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('4', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('5', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('6', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('7', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('8', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;
    SET @tmp_position = CHARINDEX('9', @instring); IF (@tmp_position > 0 AND @tmp_position < @position) SET @position = @tmp_position;

    IF (@position = 5000) RETURN 0;
    RETURN @position;
END
GO

DROP FUNCTION IF EXISTS dbo.udf_NaturalSortFormat;  
GO 
CREATE FUNCTION dbo.udf_NaturalSortFormat (@instring nvarchar(4000), @numberLength int = 10, @sameOrderChars nchar(50) = '')
RETURNS nvarchar(4000)
AS
BEGIN
	DECLARE @sortString nvarchar(4000);
    DECLARE @numStartIndex int;
    DECLARE @numEndIndex int;
    DECLARE @padLength int;
    DECLARE @totalPadLength int;
    DECLARE @i int;
    DECLARE @sameOrderCharsLen int;

    SET @totalPadLength = 0;
    SET @instring = TRIM(@instring);
    SET @sortString = @instring;
    SET @numStartIndex = dbo.udf_FirstNumberPos(@instring);
    SET @numEndIndex = 0;
    SET @i = 1;
    SET @sameOrderCharsLen = LEN(@sameOrderChars);

    WHILE (@i <= @sameOrderCharsLen)
	BEGIN
        SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' ');
        SET @i = @i + 1;
    END

    WHILE (@numStartIndex <> 0)
	BEGIN
        SET @numStartIndex = @numStartIndex + @numEndIndex;
        SET @numEndIndex = @numStartIndex;

        WHILE (dbo.udf_FirstNumberPos(SUBSTRING(@instring, @numEndIndex, 1)) = 1)
            SET @numEndIndex = @numEndIndex + 1;

        SET @numEndIndex = @numEndIndex - 1;

        SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex);

        IF @padLength < 0
            SET @padLength = 0;

        SET @sortString = STUFF(@sortString, @numStartIndex + @totalPadLength, 0, REPLICATE('0', @padLength));
		
        SET @totalPadLength = @totalPadLength + @padLength;
        SET @numStartIndex = dbo.udf_FirstNumberPos(RIGHT(@instring, LEN(@instring) - @numEndIndex));
    END

    RETURN @sortString;
END
GO