Order by only the numeric part of a string

Question

I have a column called Number populated with this data (column is nchar):

1
2
1091
3
20
2B

I want a select statement that give this order:

1
2
2B
3
20
1091

How I can accomplish this? Thanks everybody for your help

(EDITED)

Answer 1

You can perform some tricks by converting to a numeric after you discover the location of the first non-numeric. Appending a random character at the end makes it treat all strings the same even if the original string did not contain an alphabetic.

SELECT [Number] FROM dbo.TableName 
ORDER BY CONVERT(INT, LEFT(Number, PATINDEX('%[^0-9]%', Number + 'z')-1));