SQL 實現任意進制轉十進制：Oracle 範例可移植至其他資料庫

利用 POWER 函數與位元拆解，單一 SQL 完成任意進制轉十進制。以下以 Oracle 語法示範，邏輯可移植至 SQL Server、PostgreSQL、MySQL 等。

二進制轉十進制

SELECT SUM(data1)
FROM (
  SELECT SUBSTR('1101', ROWNUM, 1) * POWER(2, LENGTH('1101') - ROWNUM) AS data1
  FROM dual
  CONNECT BY ROWNUM <= LENGTH('1101')
)

邏輯： 逐字元取得位元值 × 2^位置權重，彙總求和。

八進制轉十進制

SELECT SUM(data1)
FROM (
  SELECT SUBSTR('1101', ROWNUM, 1) * POWER(8, LENGTH('1101') - ROWNUM) AS data1
  FROM dual
  CONNECT BY ROWNUM <= LENGTH('1101')
)

僅將底數從 2 改為 8。

十六進制轉十進制 (含 A-F 處理)

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) AS data1
  FROM dual
  CONNECT BY ROWNUM <= LENGTH('2D')
)

通用化建議

1. 封裝為函數 fn_ConvertBaseToDec(input_str, base)
2. 參數化輸入字串與底數 (2/8/16/36 等)
3. 十六進制以上需擴充字元對照表 (0-9, A-Z)
4. SQL Server 移植：CONNECT BY 改用遞迴 CTE 或 STRING_SPLIT + ROW_NUMBER

SQL Server 移植範例 (二進制)

WITH bits AS (
  SELECT 1 AS pos, SUBSTR('1101', 1, 1) AS bit
  UNION ALL
  SELECT pos+1, SUBSTR('1101', pos+1, 1)
  FROM bits WHERE pos < LEN('1101')
)
SELECT SUM(CAST(bit AS INT) * POWER(2, LEN('1101') - pos)) FROM bits

掌握位元權重累加原理，即可在任何支援遞迴/階層查詢的資料庫實作通用進制轉換函數。