sql转换进制 oracle,ORACLE中用一条SQL实现其它进制到十进制的转换

-----二进制转换十进制-----------------

select sum(data1)

from (select substr('1101', rownum, 1) * power(2, length('1101') - rownum) data1

from dual

connect by rownum <= length('1101'))

[@more@]

-----八进制转换十进制-----------------

select sum(data1)

from (select substr('1101', rownum, 1) * power(8, length('1101') - rownum) data1

from dual

connect by rownum <= length('1101'))

----十六进制转换十进制-----------------

select sum(data1)

from (select (CASE upper(substr('2D', rownum, 1))

WHEN 'A' THEN '10'

WHEN 'B' THEN '11'

WHEN 'C' THEN '12'

WHEN 'D' THEN '13'

WHEN 'E' THEN '14'

WHEN 'F' THEN '15'

ELSE substr('2D', rownum, 1)

END) * power(16, length('2D') - rownum) data1

from dual

connect by rownum <= length('2D'))

一 16进制转换为10进制

可以通过to_number函数实现

SQL> select to_number(’19f’,’xxx’) from dual;

TO_NUMBER(’19F’,’XXX’)

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

415

SQL> select to_number(’f’,’xx’) from dual;

TO_NUMBER(’F’,’XX’)

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

15

二 10进制转换为16进制

可以通过to_char函数转换

SQL> select to_char(123,’xxx’) from dual;

TO_C

----

7b

SQL> select to_char(4567,’xxxx’) from dual;

TO_CH

-----

11d7

三 2进制转换为10进制

从Oracle9i开始,提供函数bin_to_num进行2进制到10进制的转换

SQL> 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