Sort Order for varying length string.

[robbiez]

Hi
I have searched for an answer but can't even find a starting point.

I have a list of order number based on a prefixed date stamp:
ORD201302161, ORD201302162, ORD201302163, ORD201302164, etc.

The display is sorted using ASC, however they do not display as I would expect them to:

ORD201302161
ORD2013021611
ORD2013021612
ORD2013021613
ORD2013021614
ORD2013021615
ORD2013021616
ORD2013021617
ORD2013021618
ORD2013021619
ORD201302162
ORD2013021620

How can I sort the order correctly??

Thanks
Rob

[BubikolRamios]

This is coz it is sorted as string, that is, if you would sort this numbers, as string, they would be sorted like this:

1
11
2

here is the hint:

select 'ORD201302161', RIGHT('ORD201302161',LENGTH('ORD201302161')-3), RIGHT('ORD201302161',LENGTH('ORD201302161')-3) + 0

order by last resulting field, '+ 0' converts char to int

[Old Pedant]

Easier than Bubikol's suggestion:

SELECT list, of, fields 
FROM youtable
ORDER BY CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )

That assumes that all your values in that field indeed start with 'ORD' so we can ignore the first 3 characters.

If the values might start with other 3-letter sequences, and if you want the values in order, including the letters, as for example:

ABC331
ABC2017
DEF9
DEF1311
ORD771
ORD2468

Then do this:

SELECT list, of, fields 
FROM youtable
ORDER BY LEFT(yourOrdField,3), CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )