Order By CHAR fields in DB2

Geetha B · New User Joined: 24 Nov 2007 Posts: 12 Location: chennai

Hi,

We have a requirement where we need to order by on a char field in such a way that we will get all the char fields which has numeric values and then we should get char fields containing other than numeric values.

For e.g.:

Table column has following values:
AAAA
BBBB
1009
100A
1010
101B
2000
3010

req o/p:

1009
1010
2000
3010
100A
101B
AAAA
BBBB

Please let us know how we can achieve the above results.

Thanks

GuyC · Posted: Thu Jul 01, 2010 6:18 pm

in Unicode you already get 0..9 A..Z
in Ebcdic you get A..Z0..9
so you can experiment to get your data in the correct CCSID

or use collation_key()

dick scherrer · Posted: Thu Jul 01, 2010 8:04 pm

Hello,

Depending on your overall requirement (not just this one sequence), suggest you convince "them" that on the mainframe, the sequence is not the same as on the pc.

Even when this process "works" there will very likely be problems when the data is used in other mainframe processes. The A will compare less than the zero in all of the program code. . .