to_dec for Oracle

djberg96 on 2004-08-04T20:34:03

We had some data that was, for reasons I dare not ask, in hex instead of decimal. Surprisingly there is no Oracle SQL function to convert numbers of one base to another (that I saw - if there is, please let me know). The convert() function in Sybase can do this, but the Oracle version works differently.

Anyway I came across this: create or replace function to_dec ( p_str in varchar2, p_from_base in number default 16 ) return number is l_num number default 0; l_hex varchar2(16) default '0123456789ABCDEF'; begin if ( p_str is null or p_from_base is null ) then return null; end if; for i in 1 .. length(p_str) loop l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1; end loop; return l_num; end to_dec; / Works like a charm.

---

Shameless self-promotion

jhorwitz on 2004-08-04T23:27:44

Or you could use extproc_perl, though it's a bit of overkill for a simple hex converter:

sub to_dec { hex($_[0]); }

In SQL*Plus:

SQL> select to_dec('2A') from dual;

TO_DEC('2A')
------------
42

Re:Shameless self-promotion

djberg96 on 2004-08-05T04:42:35

Heh, nice. One of my missions in life is to port your code in order to create extproc_ruby. :)