-- =================================================
-- Author:		Theodore Brown
-- Create date: 2016-12-02
-- Description:	Sort alphanumeric strings naturally!
-- =================================================
CREATE FUNCTION [dbo].[fnNaturalSort]
(
	@string nvarchar(255)
)
RETURNS nvarchar(264)
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