'Reverse EBCDIC sorts numbers before letters in ROW_NUMBER function
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.
Solution 1:[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
Solution 2:[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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Pwnstar |
| Solution 2 | Bruce |