# Thread: sorting a character column

1. Registered User
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. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
How would you like to see sorted table ?

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

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

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

5. Registered User
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. Registered User
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. Registered User
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.```

8. Registered User
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. Registered User
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. Registered User
Join Date
Oct 2009
Posts
4
Thanks one and all for the valuable assistance.
It sorts perfectly by using
ORDER By
LTRIM(Charcol)

11. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
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. Registered User
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.

#### Posting Permissions

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