Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Order By Alphanumeric values sort on table

    Hello,

    I am trying to sort a result set of rows on a column that has alphanumeric values:


    'E1'
    'E2'
    'E3'
    'E4'
    'E5'
    'E6'
    'E7'
    'E8'
    'E9'
    'O1'
    'O10' <---- this value needs to be ......
    'O2'
    'O3'
    'O4'
    'O5'
    'O6'
    'O7'
    'O8'
    'O9'
    ......... here
    'W1'
    'W2'
    'W3'
    'W4'
    'W5'

    the 'O10' (O being Alphabetic letter O) needs to be placed after the 'O9'
    value. I figure it is a casting/translate issue into a numeric but I need the remaining
    columns to remain as is. Not sure how to work that into an 'order by'


    Thanks in advance


    Mike

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you always have 1 letter followed by some number, you could piece it apart like this:
    Code:
    SELECT ...
    FROM ...
    WHERE ...
    ORDER BY SUBSTR(col, 1, 1), INT(SUBSTR(col, 2))
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    Thanks Knut.. that worked perfectly... have a great day

Posting Permissions

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