Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
02-16-2013, 05:26 PM #1
- Join Date
- Jul 2008
- Thanked 0 Times in 0 Posts
Sort Order for varying length string.
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:
How can I sort the order correctly??
02-16-2013, 11:55 PM #2
- Join Date
- Dec 2005
- Thanked 76 Times in 76 Posts
This is coz it is sorted as string, that is, if you would sort this numbers, as string, they would be sorted like this:
here is the hint:
order by last resulting field, '+ 0' converts char to intCode:select 'ORD201302161', RIGHT('ORD201302161',LENGTH('ORD201302161')-3), RIGHT('ORD201302161',LENGTH('ORD201302161')-3) + 0
02-17-2013, 02:21 AM #3
Easier than Bubikol's suggestion:
That assumes that all your values in that field indeed start with 'ORD' so we can ignore the first 3 characters.Code:SELECT list, of, fields FROM youtable ORDER BY CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )
If the values might start with other 3-letter sequences, and if you want the values in order, including the letters, as for example:
Then do this:Code:ABC331 ABC2017 DEF9 DEF1311 ORD771 ORD2468
Code:SELECT list, of, fields FROM youtable ORDER BY LEFT(yourOrdField,3), CAST( SUBSTR( yourOrdField, 4 ) AS DECIMAL )
Last edited by Old Pedant; 02-17-2013 at 02:26 AM.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.