| View previous topic :: View next topic |
| Author |
Message |
geetha001
Beginner
Joined: 22 Jun 2005
Posts: 41
Topics: 14
|
 Posted: Mon Oct 17, 2011 8:59 pm Post subject: ORDER BY a DB2 varchar column - 1st char being different |
 |
|
The first character of a DB2 table varchar column can be a special character/Numeric/Alphabet. How can I perform an ORDER BY so that the result contains all special characters first, then numeric and then alphabet.
Example: Values of a column include
| Code: |
<test1
@test2 column
Abb char
Bcc char
1test char
2test space
|
Results are required as
| Code: |
<test1
@test2 column
1test char
2test space
Abb char
Bcc char
|
|
|
| Back to top |
|
 |
papadi
Supermod
Joined: 20 Oct 2009
Posts: 594
Topics: 1
|
| Posted: Mon Oct 17, 2011 10:48 pm Post subject: |
|
|
Suggest you reconsider what you want if this is to be used on the mainframe.
If you get the values in the sequence you say you want, they will be pretty much useless when used to compare against each other or other "normal"ly sequenced data. The data will NOT be in the system collating sequence. . .
Why does someone believe this "sequence" will be useful?
_________________
All the best,
di |
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Tue Oct 18, 2011 4:15 am Post subject: |
|
|
| Code: | | order by position(substr(col1,1,1),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',octets), substr(col1,2) |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Oct 18, 2011 10:42 am Post subject: |
|
|
thx GuyC,
another keeper. much easier than figuring it out on my own.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Wed Oct 19, 2011 3:41 am Post subject: |
|
|
| dbzTHEdinosauer wrote: | thx GuyC,
another keeper. much easier than figuring it out on my own. |
you're welcome.
It's just another implementation for 'user defined sortsequence'
I do like this one :
| Code: | | order by position(substr(col1,1,3),'MonTueWedThuFriSatSun',octets) |
Ps.: I didn't invent this, I've read it somewhere (and remembered). |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
