### 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:

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

Here is the original code:

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;

/

## 4 Comments:

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?

@Randy

what will be the expected hex value for it?You'll have to tell me - how would you like it represented??!?!

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

Thanks very much.

It was very important for me and

it's very dificult to find.

POST A COMMENT

<< Home