| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: hex 2 dec !
In article <384C2DA4.7ECF_at_intrasoft.gr>,
dinos5_at_yahoo.gr wrote:
> hi all,
>
> Could anyone tell me where can I find a fuction in PL/SQL that
coverts a
> hexdecimal number to decimal and vice versa. Has anyboby already
> implemented that function?
>
> 10q
>
Here is a post that appeared on this board earlier this year. The
author is very good so maybe this will help you.
Re: Help! Hex to Dec???
Author: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1999/02/11
Forum: comp.databases.oracle.misc
by 'MS' byte ordering do you really mean "i've got twos compliment
binary
integer integers"? What is 'MS' byte ordering? (youre not giving them
credit
for inventing the storage of 2's compliment are you? its really part
of the
chip architecuture).....
anyway, if you are -- here is a set of pl/sql routines that might do the trick... I did it on solaris and i'm pretty sure the byte order on intel platforms is the same (if not, reverse some loops below)....
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
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;
to_dec takes a number in any base upto 16 and converts it into decimal...
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 ( 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;
to_base takes a decimal number and converts it into a base...
create or replace function bin_to_dec( l_x in varchar2 ) return number
as
begin
if (substr(l_x,1,1) in ( '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' ) ) then
return -1*(to_dec(translate(to_base(to_dec(l_x),2),'10','01'),2) +1);
else
return to_dec( l_x );
end if;
end;
/
Function created.
bin_to_dec is the function you want. it takes a VARCHAR as input (the
hex
representation of the RAW(4) field you have -- just send it the RAW, the
database will convert it to hex automagically -- an example is below)...
It returns the decimal representation of that (assuming a 2's
compliment number
that is)...
It does this by looking at the first hex character, if the high bit is
on (thats
the 8...F check), then its a negative number. If the number is
negative we:
o to_dec(l_x) -> tmp1 -> that converts the hex FFFFFFFF into an
unsigned number
o to_base( tmp1 ) -> tmp2 -> that converts the decimal unsigned number
into
binary
o translate(tmp2,'10','01') -> tmp3 -> that does an XOR for us
o to_dec(tmp3,)2) -> tmp4 -> converts the binary into decimal again
o tmp4+1 -> tmp5 -> adjusts for the 2's compliment
o -1 * tmp5 -> answer -> make it negative...
If the number is not negative, we just return 'to_dec(hexstring)'
Here is an example with example data:
SQL> drop table raw_data;
Table dropped.
SQL> create table raw_data( dec int, bin raw(4) ); Table created.
This table will hold a decimal number and the 4 byte RAW version of
that number.
We'll use the dec column to verify our results later..
SQL> insert into raw_data values ( -10000, 'FFFFD8F0' ); SQL> insert into raw_data values ( -9000, 'FFFFDCD8' ); SQL> insert into raw_data values ( -8000, 'FFFFE0C0' ); SQL> insert into raw_data values ( -7000, 'FFFFE4A8' ); SQL> insert into raw_data values ( -6000, 'FFFFE890' ); SQL> insert into raw_data values ( -5000, 'FFFFEC78' ); SQL> insert into raw_data values ( -4000, 'FFFFF060' ); SQL> insert into raw_data values ( -3000, 'FFFFF448' ); SQL> insert into raw_data values ( -2000, 'FFFFF830' ); SQL> insert into raw_data values ( -1000, 'FFFFFC18' ); SQL> insert into raw_data values ( 0, '00000000' ); SQL> insert into raw_data values ( 1000, '000003E8' ); SQL> insert into raw_data values ( 2000, '000007D0' ); SQL> insert into raw_data values ( 3000, '00000BB8' ); SQL> insert into raw_data values ( 4000, '00000FA0' ); SQL> insert into raw_data values ( 5000, '00001388' ); SQL> insert into raw_data values ( 6000, '00001770' ); SQL> insert into raw_data values ( 7000, '00001B58' ); SQL> insert into raw_data values ( 8000, '00001F40' ); SQL> insert into raw_data values ( 9000, '00002328' );
the above are just a sampling of positve and negative numbers...
SQL> insert into raw_data values ( -1, 'FFFFFFFF' ); SQL> insert into raw_data values ( 0, '00000000' ); SQL> insert into raw_data values ( 1, '00000001' ); SQL> insert into raw_data values ( -2147483648, '80000000' ); SQL> insert into raw_data values ( 2147483647, '7FFFFFFF' ); SQL> commit;
the above are all of the boundary value numbers, ones that would give
us a hard
time...
Now we can simply:
SQL> select dec, bin_to_dec( bin ) from raw_data 2 /
DEC BIN_TO_DEC(BIN)
--------------- ---------------
-10000 -10000
-9000 -9000
-8000 -8000
-7000 -7000
-6000 -6000
-5000 -5000
-4000 -4000
-3000 -3000
-2000 -2000
-1000 -1000
0 0
1000 1000
2000 2000
3000 3000
4000 4000
5000 5000
6000 6000
7000 7000
8000 8000
9000 9000
-1 -1
0 0
1 1
-2147483648 -2147483648
2147483647 2147483647
25 rows selected.
and see that it works....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Dec 07 1999 - 10:39:35 CST
 |
 |