CREATE FUNCTION dbo.RemoveChars( @Input varchar(1000))

RETURNS VARCHAR(100)

BEGIN

  DECLARE @pos int

  SET @Pos = PATINDEX( '%[^0-9]%', @Input)

  WHILE @Pos > 0 

  BEGIN

    SET @Input = STUFF( @Input, @pos, 1, '')

    SET @Pos = PATINDEX( '%[^0-9]%', @Input)

  END

  RETURN @Input

END

GO

CREATE TABLE #Test( [Column] varchar(10))

INSERT INTO #Test SELECT '11' AS 'Column'

INSERT INTO #Test SELECT '10' AS 'Column'

INSERT INTO #Test SELECT '2' AS 'Column'

INSERT INTO #Test SELECT '3' AS 'Column'

INSERT INTO #Test SELECT '1' AS 'Column'

INSERT INTO #Test SELECT '3A' AS 'Column'

INSERT INTO #Test SELECT '3B' AS 'Column'

INSERT INTO #Test SELECT '3C' AS 'Column'

SELECT * FROM #Test ORDER BY CONVERT( integer, dbo.RemoveChars( [Column])) ASC

DROP TABLE #Test

DROP FUNCTION dbo.RemoveChars