1

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).

2

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 <> '' then 1 else 2 end, 
         alpha, 
         num
  • I get the following error on the first cross : "SQL80001: Incorrect syntax near 'cross'. Expecting AS, ID or Quoted_ID." – A.bakker Jan 16 at 7:54
0

I managed to find the solution in the first answer to the question here:

https://stackoverflow.com/questions/29676432/alphanumeric-sort

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.