Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jul 2008
    Posts
    74
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    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:

    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
    Last edited by BubikolRamios; 02-16-2013 at 10:58 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,550
    Thanks
    78
    Thanked 4,382 Times in 4,347 Posts
    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 )
    Last edited by Old Pedant; 02-17-2013 at 01: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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •