Oracle進(jìn)制轉(zhuǎn)換函數(shù)

發(fā)布時(shí)間: 2012/8/24 17:17:25

Oracle中沒有16進(jìn)制轉(zhuǎn)2進(jìn)制的函數(shù)，為了工作方便，自己寫了一個(gè)

create or replace function hex_to_bin(hex varchar2) return varchar2 is
v_hex varchar2(10000);
v_len number;
-

v_var    varchar2(10000);
v_result varchar2(10000) := '';
begin
v_hex := upper(hex);
v_len := length(v_hex);
for i in 1 .. v_len loop
    v_var    := case substr(v_hex, i, 1) when '0' then '0000' when '1' then '0001' when '2' then '0010' when '3' then '0011'
    when '4' then '0100' when '5' then '0101' when '6' then '0110' when '7' then '0111' when '8' then '1000' when '9' then '1001'
    when 'A' then '1010' when 'B' then '1011' when 'C' then '1100' when 'D' then '1101' when 'E' then '1110' when 'F' then '1111' end;
    v_result := v_result || v_var || ' ';
end loop;
return v_result;
end hex_to_bin;

例子：

SQL> select hex_to_bin('0A11') from dual;

HEX_TO_BIN('0A11')

--------------------------------------------------------------------------------

0000 1010 0001 0001

16進(jìn)制轉(zhuǎn)10進(jìn)制，Oracle里面有個(gè)to_number函數(shù) 可以實(shí)現(xiàn)
SQL> select to_number('ABC','XXX') from dual;
TO_NUMBER('ABC','XXX')
----------------------
2748
10進(jìn)制轉(zhuǎn)16進(jìn)制，ORACLE里面有個(gè)to_char函數(shù) 可以實(shí)現(xiàn)
SQL> select to_char('2748','XXX') from dual;
TO_CHAR('2748','XXX')
---------------------
ABC
2進(jìn)制轉(zhuǎn)10進(jìn)制，ORACLE里面有個(gè)bin_to_num函數(shù)可以實(shí)現(xiàn)
SQL> select bin_to_num(1,1,1,1,0) from dual;
BIN_TO_NUM(1,1,1,1,0)
---------------------