Thursday, October 08, 2009

http://asktom.oracle.com/tkyte/hexdec

Base Conversion Routines

This set of routines is useful to convert between various 'bases' in Oracle. Once you install these functions, you will be able to perform operations such as:


SQL> select to_bin( 123 ) bin, to_hex( 123 ) hex, to_oct( 123 ) oct from dual
2 /

BIN HEX OCT
--------------- --------------- ---------------
1111011 7B 173

SQL>
SQL> select to_dec( '1111011', 2 ) base2, to_dec( '7B' ) base16,
2 to_dec('173',8) base8
3 from dual
4 /

BASE2 BASE16 BASE8
---------- ---------- ----------
123 123 123


Of course, with current releases - you would never user TO_HEX since TO_CHAR already does this (faster). Also TO_NUMBER would be used to convert HEX to decimal these days as well


ops$tkyte%ORA10GR2> select to_char( '1234', 'XXXX' ) from dual;

TO_CH
-----
4D2




Here is the original code:


create or replace 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(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null )
then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
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;
/


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;
/
show errors

create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/

POST A COMMENT

4 Comments:

Anonymous Randy Dolores said....

Hi Tom,

Thanks for this great info.

Just wanted to ask some help.

But what if the case is I want to convert a string to hex, is there a function in Oracle to do that:

Something like this:
Input string is "TOR-DT-QA04/303"

What will be the expected hex value for it?

Thu Nov 12, 09:41:00 AM EST  

Blogger Thomas Kyte said....

@Randy

what will be the expected hex value for it?

You'll have to tell me - how would you like it represented??!?!

Thu Nov 12, 10:07:00 AM EST  

Anonymous Frank Zhou said....

Hi Tom,

Here is a sql solution for converting a number from any base to any other base.

http://oraqa.com/2008/04/19/how-to-convert-number-from-any-base-to-any-other-bases-in-sql/

Thanks,

Frank

Fri Jan 01, 01:28:00 PM EST  

Blogger Luis Fernando Ramos said....

Thanks very much.

It was very important for me and
it's very dificult to find.

Tue Jul 23, 11:27:00 AM EDT  

POST A COMMENT

<< Home