Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Exclamation Unanswered: Sorting strings in Alphanumeric order

    Hai

    Is there a way to sort a string column in ascending order with aplha first and then numeric.
    For example,

    If the column productid has values A100,2030,B223 and 1002,
    the sort order must be
    A100
    B223
    1002
    2030

    Normal ascending order in SQl query gives sorting as 1002, 2030, A100 and B223.

    Please help

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could do this:
    Code:
    order by case when substr(productid,1,1) between '0' and '9'
             then 2 else 1 end,
             productid

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Code:
    alter session set nls_sort = 'LATIN';
    select productid from prod order by productid;
    
    A100
    B223
    1002
    2030
    
    alter session set nls_sort = 'BINARY';
    select productid from prod order by productid;
    
    1002
    2030
    A100
    B223
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    The things you learn ...
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    A variation of Bill's answer
    Code:
    SQL@9iR2> select productid
      2    from prod
      3   order by nlssort( productid, 'nls_sort = latin' );
    
    PRODUCTID
    ----------
    A100
    B223
    1002
    2030
    
    SQL@9iR2>

Posting Permissions

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