Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: sorting a character column

    I am trying to sort a column defined as char length of 10.
    Values are right adjusted. Example:
    ' 050120'
    ' 050149'
    ' 050250'
    ' 060100'
    ' 050149R'
    I would like the values 050149 & 050149R sorted together, instead of having the 050149R show up at the end. I saw a similar post but haven't been able to accomplish the task. Would appreciate any help.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    How would you like to see sorted table ?

  3. #3
    Join Date
    Oct 2009
    Posts
    4
    For the example given, this would be the sorted results:
    ' 050120'
    ' 050149'
    ' 050149R'
    ' 050250'
    ' 060100'

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    For example:

    select charcol, t1.*
    from table1 t1
    order by left(charcol, 6)
    Lenny

  5. #5
    Join Date
    Oct 2009
    Posts
    4
    I just tried that LEFT scalar function and the values with an Alpha on the end are still grouped at the end of the sort. It must be because the values that have a trailing alpha begin in position 4 of the field and those that don't have the alpha character begin in position 5 of the field.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    So, you can try:

    select charcol, t1.*
    from table1 t1
    order by substr(charcol, 1, 6), charcol
    Lenny

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    String comparison goes throw 2 strings character-by-character and if the character in the first string comes before the corresponding character in the other string, the first string is lexicographically sorted before the other string. In your case, you reach the point where a space is compared with '0', which leads to all strings with a space in that position to be sorted before all strings with a '0'. What you have to do is get rid of the leading spaces. A function like LTRIM() may be what you need, e.g. ORDER BY LTRIM(myCol).

    Code:
    $ db2 "select * from table ( values ('  050120'),('  050149'),('  050250'),('  060100'),(' 050149R') ) as t(c) order by c"
    
    C
    --------
      050120
      050149
      050250
      060100
     050149R
    
      5 record(s) selected.
    
    $ db2 "select * from table ( values ('  050120'),('  050149'),('  050250'),('  060100'),(' 050149R') ) as t(c) order by ltrim(c)"
    
    C
    --------
      050120
      050149
     050149R
      050250
      060100
    
      5 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Oh, I did not see space in position1:

    select charcol, t1.*
    from table1 t1
    order by left(charcol, 7)
    or

    select charcol, t1.*
    from table1 t1
    order by substr(charcol, 2, 6), charcol
    Lenny

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    @Lenny77:

    The values are right adjusted. There are different number of preceeding blancs. Therefore SUBSTR() or LEFT() don't work.

    As stolze mentioned, LTRIM() [ ... or STRIP() ] is the solution

  10. #10
    Join Date
    Oct 2009
    Posts
    4
    Thanks one and all for the valuable assistance.
    It sorts perfectly by using
    ORDER By
    LTRIM(Charcol)

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by umayer
    @Lenny77:

    The values are right adjusted. There are different number of preceeding blancs. Therefore SUBSTR() or LEFT() don't work.

    As stolze mentioned, LTRIM() [ ... or STRIP() ] is the solution
    Oh, boy !
    You are right. I did not read about right adjusting !

    Lenny

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Bad formatting in the original post, I guess. Without right-adjustment, you don't need to do anything - just sort the left-adjusted values and that's it.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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