Home > Software design >  Reverse EBCDIC sorts numbers before letters in ROW_NUMBER function
Reverse EBCDIC sorts numbers before letters in ROW_NUMBER function

Time:05-11

So I do have following SQL select

SELECT FOO.*, ROW_NUMBER() OVER (ORDER BY KEY ASC) AS ROW_NUMBER
FROM FOO

enter image description here

Key is of the type VARCHAR(12). I wonder why the hell the numbers are sorted after the letters. Every other system including UTF-8 always begins with numbers.

enter image description here

CodePudding user response:

DB2 is likely using EBCDIC as it's default character set for sorting, which has numbers after letters: http://astrodigital.org/digital/ebcdic.html

CodePudding user response:

So a solution you can do is to take advantage of the EBCDIC character order. Special characters are sorted before the letters!

If the numeric values are replaced, for example, like 0 -> .0, the sorting automatically works correctly. As this means there is no "conversion" to ASCII involved it's also not so expensive if a lot data is sorted.

RAW_DATA CONVERTED FOR ROW_NUMBER ORDER BY ROW_NUMBER
ABC ABC .0.0.1
A1C A.1C .0.1AF
0A1 .0A.1 .0A.1
001 .0.0.1 .0A.1B
A01 A.0.1 .1.0A
10A .1.0A A.0.1
ADFG ADFG A.0B.1.1
01AF .0.1AF A.1C
0A1B .0A.1B AB.0.1.0
BA0 BA.0 ABC
A0B11 A.0B.1.1 BA.0
AB010 AB.0.1.0 ADFG

And the query for that:

SELECT FOO.*, 
ROW_NUMBER() OVER (ORDER BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(KEY, '0', '.0'),'1', '.1'),'2', '.2'),'3', '.3'),'4', '.4'),'5', '.5'),'6', '.6'),'7', '.7'),'8', '.8'),'9', '.9') ASC) AS ROW_NUMBER
FROM FOO
  • Related