By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
/
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
| NUM | CONVERT32TODECIMAL(NUM) |
|---|---|
| 13r | 1147 |
| H | 17 |
| 8 | 8 |
| 5 | 5 |