How to sort values starting with a letter followed up by a number? I have a series of values, possibilities are : A1, A2, B1, B2, A100, A101, B100, B200, 1, 2, 3, 4 and so on. 

How can I sort it so that first the letter is sorted, and after that the number with the numbers from the previous list would be going like this:

A1
A2
A100
A101
B1
B2
B200
1
2
3
4


The list itself is a lot larger and not really predefined so preferably no solutions in which each value needs to get handled separately. 

I'm using Microsoft SQL Server 2016 (SP1) (KB3182545).
You need to parse the string to separate the alpha + numeric part.

Assuming that it is always alpha first follow by numeric and not the other way round

the 2 cross apply split the alpha and numeric part from the string

select  *
from    yourtable t
        cross apply
        (
            select  alpha   = LEFT(your_col, PATINDEX('%[^0-9]%', your_col))
        ) AS a
        cross apply
        (
            select  num = CONVERT(INT, REPLACE(your_col, alpha, ''))
        ) AS n 
order by case when alpha &lt;&gt; '' then 1 else 2 end, 
         alpha, 
         num