Oracle SQL 單一陳述式實現多進制轉十進制：二進位、八進位、十六進位通用寫法

核心原理

利用 CONNECT BY LEVEL 產生位數序列，SUBSTR 逐字元提取，POWER 計算權重，SUM 累加。十六進位額外需 CASE 轉換 A-F。

範例查詢

二進位 → 十進位

SELECT SUM(bit_val)
FROM (
  SELECT SUBSTR('1101', LEVEL, 1) * POWER(2, LENGTH('1101') - LEVEL) bit_val
  FROM dual
  CONNECT BY LEVEL <= LENGTH('1101')
);

八進位 → 十進位

SELECT SUM(oct_val)
FROM (
  SELECT SUBSTR('1101', LEVEL, 1) * POWER(8, LENGTH('1101') - LEVEL) oct_val
  FROM dual
  CONNECT BY LEVEL <= LENGTH('1101')
);

十六進位 → 十進位

SELECT SUM(hex_val)
FROM (
  SELECT 
    CASE UPPER(SUBSTR('2D', LEVEL, 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 TO_NUMBER(SUBSTR('2D', LEVEL, 1))
    END * POWER(16, LENGTH('2D') - LEVEL) hex_val
  FROM dual
  CONNECT BY LEVEL <= LENGTH('2D')
);

封裝為通用函數建議

CREATE OR REPLACE FUNCTION base_to_dec(p_str VARCHAR2, p_base NUMBER) RETURN NUMBER IS
  v_sum NUMBER := 0;
BEGIN
  FOR i IN 1..LENGTH(p_str) LOOP
    v_sum := v_sum * p_base + 
      CASE 
        WHEN p_base > 10 AND UPPER(SUBSTR(p_str,i,1)) BETWEEN 'A' AND 'F'
        THEN ASCII(UPPER(SUBSTR(p_str,i,1))) - ASCII('A') + 10
        ELSE TO_NUMBER(SUBSTR(p_str,i,1))
      END;
  END LOOP;
  RETURN v_sum;
END;
/

應用場景

• ETL 處理含進制編碼的原始資料
• 無前端程式支援時的臨時轉換
• 教學示範位權加權原理