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