Algorithm to sort strings mixed with Alpha and Numeric values.

CREATE FUNCTION dbo.fnSplitType
(
@Data VARCHAR(200),
@PartSize TINYINT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000),
@Alpha TINYINT,
@OldPosition SMALLINT,
@NewPosition SMALLINT

SELECT @Result = '',
@Alpha = 1,
@OldPosition = 1,
@NewPosition = 1

IF @Data LIKE '[0-9]%'
SELECT @Result = REPLICATE(' ', @PartSize),
@Alpha = 0

WHILE @NewPosition < LEN(@Data)
SELECT @NewPosition = CASE @Alpha
WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000))
ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000))
END,
@NewPosition = CASE @NewPosition
WHEN 0 THEN LEN(@Data)
ELSE @OldPosition + @NewPosition - 2
END,
@Result = @Result + CASE @Alpha
WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize)
ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize)
END,
@Alpha = 1 - @Alpha,
@OldPosition = @NewPosition + 1

RETURN RTRIM(@Result)
END

Here is the code to test with

DECLARE @Sample TABLE (Info VARCHAR(200))

INSERT @Sample
SELECT 'S0C 4610' UNION ALL
SELECT 'S9C 113' UNION ALL
SELECT 'S1C 462' UNION ALL
SELECT '112' UNION ALL
SELECT '113' UNION ALL
SELECT 'MM20BC' UNION ALL
SELECT 'SSC 113' UNION ALL
SELECT 'SSC 201' UNION ALL
SELECT 'SSC 461' UNION ALL
SELECT 'SSC 4610' UNION ALL
SELECT 'SSC 462' UNION ALL
SELECT 'SSCPZ202C' UNION ALL
SELECT 'Z1' UNION ALL
SELECT 'Z100' UNION ALL
SELECT 'ZZ'

SELECT Info
FROM @Sample
ORDER BY dbo.fnSplitType(Info, 8)