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

Yes Praveen, the column has hundreds of values. I just chose a representative sample to explain the problem — Neil J
– Neil J, Commented Mar 19, 2013 at 13:02

Indi · Accepted Answer · 2013-03-19 13:04:08Z

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 |

answered Mar 19, 2013 at 13:04

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Neil J Over a year ago

Sorry JohnD but what do you mean by upvote? I'm assuming it doesn't mean to mark an answer as correct.

Neil J Over a year ago

@Kaf - Just when I thought I had it all sussed I've found an allele in the table with 3 ':' separators: - 01:01:02:01 The current algorithm has this value place above 01:01 (Note, there is no 01:01:02 in the table)

Indi Over a year ago

Neil: you have to get Index3 within cte and follow the same procedure to buildup equal length strings... Thanks @JohnD for the upvote!

JohnD Over a year ago

@Neil If answer(s) are helpful to you, click the up arrow/triangle above the # at the top-left of the answer. Take a look at the FAQ section "How do I ask questions here?" - it has more detailed info.

Rachcha · Accepted Answer · 2013-03-19 12:39:48Z

0

Use

ORDER BY CAST (
             SUBSTR(Allelens
                    , 0
                    , CHARINDEX(Allelens, ':') - 1)
             AS INTEGER)

answered Mar 19, 2013 at 12:39

Rachcha

8,8969 gold badges52 silver badges70 bronze badges

Comments

JohnD · Accepted Answer · 2013-03-19 12:46:33Z

0

Do you want to sort numerically by the part before the colon? If yes, something like this should do it:

select *
from mytable
order by cast(substring(col, 0, CHARINDEX(':', col)) as int)

Results:

edited Mar 19, 2013 at 12:46

answered Mar 19, 2013 at 12:38

JohnD

14.9k4 gold badges42 silver badges54 bronze badges

1 Comment

paparazzo Over a year ago

I think it needs a second sort on number after the :

Collectives™ on Stack Overflow

Ordering numeric like strings in T-SQL

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related