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.
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