Ordering numeric like strings in T-SQL

Question

I have strings (called Alleles) in a table (called Antigens) that I'm having trouble sorting into the correct order.

A representative sample set of Alleles maybe:-

• 01:01
• 01:02
• 02:01
• 04:01
• 09:01N
• 10:01
• 104:01
• 105:01
• 11:01N
• 03:01:01
• 03:01:02

I need these Alleles to be in the following order:-

• 01:01
• 01:02
• 02:01
• 03:01:01
• 03:01:02
• 04:01
• 09:01N
• 10:01
• 11:01N
• 104:01
• 105:01

I can't sort the Alleles as strings because 104:01 & 105:01 will appear before 11:01.

I can't strip out the ':' characters and sort numerically as that will put 03:01:01 & 03:01:02 at the end as the numeric values would be 30101 & 30102 respectively.

I'm stumped as to how this can be achieved and would be grateful of any suggestions.

Cheers

Answer 1

Assuming maximum number of characters between/before/after : is 3, you could make all the string values same length and order like below. It looks bit complex though !

Fiddle demo

;with cte as (
select val, charindex(':',val,1) index1, 
            charindex(':',val,charindex(':',val,1)+1) index2
from t
)
select val,right('000' + left(val,index1-1),3) + ':' +
           case when index2-index1>0 
                then  right('000' + substring(val,index1+1,index2-index1-1),3)
           else right('000' + substring(val,index1+1,len(val)),3) end + ':' +
           case when index2>0 
                then  right('000' + right(val, len(val) - index2),3)
           else '000' end As odr

from cte
order by odr

|      VAL |         ODR |
--------------------------
|    01:01 | 001:001:000 |
|    01:02 | 001:002:000 |
|    02:01 | 002:001:000 |
| 03:01:01 | 003:001:001 |
| 03:01:02 | 003:001:002 |
|    04:01 | 004:001:000 |
|   09:01N | 009:01N:000 |
|    10:01 | 010:001:000 |
|   11:01N | 011:01N:000 |
|   104:01 | 104:001:000 |
|   105:01 | 105:001:000 |