Converting base32 to a decimal number

Question

Is there anybody who can help me converting base32 values to decimal numbers? I have a sql statement which returns me a list of base32 values. For example the return of the select looks like this:

5

8

H

13r

Now I need to figure out which of these values is the highest (in this case 13r). Therefore I would need a function to convert these base32 values to decimal numbers in order to sort them. Does anybody have such a function or does anybody have a different approach?

Answer 1

It will be little bit tricky.

You can create a function which will separate each character/digit from the original base32 number and multiply it with 32^(position in base32 number) to get decimal equivalent number and use that function in your query.

create function convert32todecimal(p_num in varchar2)
return number 
as
lv_outnum number;
begin
select sum(val) into lv_outnum from
(
  select power(32,pos) * case when d between '0' and '9' 
                then to_number(d)
                else 10 + ascii(d) - ascii('A')
           end as val
    from (
          select upper(substr(p_num,length(p_num)+1-level,1)) d,
                 level - 1 as pos
            from dual
            connect by level <= length(p_num)
         )
);
return lv_outnum;
end;
/

Now, use this function in your query as following:

with your_data(num) as (
select '5' from dual union all
select '8' from dual union all
select 'H' from dual union all
select '13r' from dual
)
select num, convert32todecimal(num)
from your_data
order by 2 desc;

db<>fiddle demo

Cheers!!