Here's a packaged and modified version of Tom Kyte's conversion routines.
--- the package
create or replace package radix
is
/*
base code courtesy of Thomas Kyte
*/
function to_base( p_dec in number, p_base in number )
return varchar2;
function to_dec (
p_str in varchar2,
p_from_base in number default 16
) return number;
function to_hex( p_dec in number ) return varchar2;
function to_bin( p_dec in number ) return varchar2;
function to_oct( p_dec in number ) return varchar2;
function to_36( p_dec in number ) return varchar2;
pragma restrict_references( to_base, wnds, rnds, wnps, rnps );
pragma restrict_references( to_dec, wnds, rnds, wnps, rnps );
pragma restrict_references( to_hex, wnds, rnds, wnps, rnps );
pragma restrict_references( to_bin, wnds, rnds, wnps, rnps );
pragma restrict_references( to_oct, wnds, rnds, wnps, rnps );
pragma restrict_references( to_36, wnds, rnds, wnps, rnps );
end radix;
/
show errors
create or replace package body radix
is
function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(36) :=
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise INVALID_NUMBER;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 )
|| l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
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(36) :=
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
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;
function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
function to_36( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 36 );
end to_36;
end radix;
/
show errors
----------------------------------------------
---------- the test ------------
select radix.to_36(100) from dual;
select radix.to_dec(radix.to_36(100),36) from dual;
select radix.to_36(255) from dual;
select radix.to_dec(radix.to_36(255),36) from dual;
-------------------------------------
Jared
"Grant Allen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/28/2003 07:45 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Base conversion
For very obscure reasons (read: one of those developer decisions that you
want to use a time-machine to go back and change), we're storing some
information in base-36 (0,1,2,3...8,9,A,B,C,...,Y,Z) in a varchar field.
And you thought hexadecimal was fun :-)
Now some bright spark would like me to build some PL/SQL to do base
conversion - in the first instance from base 36 to base 10 (i.e decimal).
Has anyone done something similar in the past ... that I could borrow or
co-opt? As you've guessed, the deadline is yesterday :-)
Ciao
Fuzzy
:-)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Grant Allen
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
