CodingForums.com - Sort Order for varying length string.

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Sort Order for varying length string. (http://www.codingforums.com/showthread.php?t=287745)

robbiez

02-16-2013 04:26 PM

Sort Order for varying length string.

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 10: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:

Code:

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 01:21 AM

Easier than Bubikol's suggestion:

Code:

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:

Code:

ABC331

ABC2017

DEF9

DEF1311

ORD771

ORD2468

Then do this:

Code:

SELECT list, of, fields 

FROM youtable

ORDER BY LEFT(yourOrdField,3), CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )

All times are GMT +1. The time now is 09:39 PM.