Reverse EBCDIC sorts numbers before letters in ROW_NUMBER function

Question

So I do have following SQL select

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

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.

Answer 1

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

Answer 2

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