Search code examples
sqloraclesql-order-byalphanumeric

Oracle : how to order hexadecimal field

Silly question but I can't find a reasonable answer.

I need to order a field containing hexadecimal values like :

select str from
    (
    select '2212A' str from dual union all
    select '2212B'     from dual union all
    select '22129'     from dual union all
    select '22127'     from dual union all
    select '22125'     from dual union all
    select '22126'     from dual
   ) t
   order by str asc;

This request give :

STR         
------------
2212A
2212B
22125
22126
22127
22129

I would like 

STR         
------------
22125
22126
22127
22129
2212A
2212B

How can I do that ?

Solution

  • Are these HEX numbers? Will the max letter be F? Then convert hex to decimal:

    select str
from t
order by to_number(str,'XXXXXXXXXXXX');

    EDIT: Stupid me. The title says it's hex numbers :P So this solution should work for you.