...

View Full Version : Sort Order for varying length string.



robbiez
02-16-2013, 05:26 PM
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
02-16-2013, 11:55 PM
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
02-17-2013, 02:21 AM
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 )



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum