一.16进制转换为10进制to_number函数
-- 此处必须为大于或等于16进制字符数的xxxxxx,此处可以为>=6个x
select to_number('1195A5','xxxxxx') from dual
TO_NUMBER('1195A5','XXXXXX')
----------------------------
1152421
select to_number('f','x') from dual;
TO_NUMBER('F','X')
------------------
15
二.10进制转换为16进制to_char函数
--此处必须为大于或等于16进制字符数的xx,此处可以为>=2个x
select to_char(123,'xx'),length(to_char(123,'xx')) from dual; --转换为16进制后前面加一个空格,长度为3
TO_ LENGTH(TO_CHAR(123,'XX'))
--- -------------------------
7b 3
三.2进制转换为10进制
select bin_to_num(1,1,0,1) a,bin_to_num(1,0) b from dual;
A B
----- ----------
13 2
SQL> select bin_to_num(1,1,1,0,1) from dual;
BIN_TO_NUM(1,1,1,0,1)
---------------------
29
四.十进制转二进制
SQL> select number_2_bit(4) from dual;
NUMBER_2_BIT(4)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100
SQL> select number_2_bit(2) from dual;
NUMBER_2_BIT(2)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
create or replace function number_2_bit(v_num number) return varchar is
v_rtn varchar(2000);
v_n1 number;
v_n2 number;
begin
v_n1 := v_num;
loop
v_n2 := mod(v_n1, 2);
v_n1 := trunc(v_n1 / 2);
v_rtn := to_char(v_n2) || v_rtn;
exit when v_n1 = 0;
end loop;
return v_rtn;
end;
--转自