單一 SQL 實現二進制、八進制、十六進制轉十進制（Oracle 語法）

核心思路

利用 CONNECT BY LEVEL 產生位置序號，逐字元取出、轉換數值、乘以基數次方、最後 SUM 彙總。

1. 二進制轉十進制

SELECT SUM(bit_val * POWER(2, len - pos)) AS dec_val
FROM (
    SELECT 
        LEVEL AS pos,
        SUBSTR('1101', LEVEL, 1) AS ch,
        LENGTH('1101') AS len,
        CASE SUBSTR('1101', LEVEL, 1) WHEN '1' THEN 1 ELSE 0 END AS bit_val
    FROM dual
    CONNECT BY LEVEL <= LENGTH('1101')
);

結果：13（1101₂ = 13₁₀）

2. 八進制轉十進制

SELECT SUM(digit_val * POWER(8, len - pos)) AS dec_val
FROM (
    SELECT 
        LEVEL AS pos,
        LENGTH('1101') AS len,
        TO_NUMBER(SUBSTR('1101', LEVEL, 1)) AS digit_val
    FROM dual
    CONNECT BY LEVEL <= LENGTH('1101')
);

結果：577（1101₈ = 577₁₀）

3. 十六進制轉十進制

SELECT SUM(hex_val * POWER(16, len - pos)) AS dec_val
FROM (
    SELECT 
        LEVEL AS pos,
        LENGTH('2D') AS len,
        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 AS hex_val
    FROM dual
    CONNECT BY LEVEL <= LENGTH('2D')
);

結果：45（2D₁₆ = 45₁₀）

通用化：封裝為函數

CREATE OR REPLACE FUNCTION base_to_dec(p_str VARCHAR2, p_base NUMBER) RETURN NUMBER
IS
    v_result NUMBER := 0;
    v_len    NUMBER := LENGTH(p_str);
BEGIN
    FOR i IN 1..v_len LOOP
        DECLARE
            v_ch   CHAR(1) := UPPER(SUBSTR(p_str, i, 1));
            v_val  NUMBER;
        BEGIN
            IF v_ch BETWEEN '0' AND '9' THEN v_val := TO_NUMBER(v_ch);
            ELSIF v_ch BETWEEN 'A' AND 'Z' THEN v_val := ASCII(v_ch) - 55;
            ELSE v_val := 0; END IF;
            v_result := v_result * p_base + v_val;
        END;
    END LOOP;
    RETURN v_result;
END;
/

-- 測試
SELECT base_to_dec('1101', 2)   FROM dual;  -- 13
SELECT base_to_dec('1101', 8)   FROM dual;  -- 577
SELECT base_to_dec('2D', 16)    FROM dual;  -- 45
SELECT base_to_dec('ZZ', 36)    FROM dual;  -- 1295

SQL Server 對應寫法

-- 使用 CTE 產生序號
WITH nums AS (
    SELECT 1 AS n UNION ALL SELECT n+1 FROM nums WHERE n < LEN(@hex)
)
SELECT SUM(
    CASE 
        WHEN SUBSTRING(@hex, n, 1) BETWEEN '0' AND '9' THEN ASCII(SUBSTRING(@hex, n, 1)) - 48
        WHEN SUBSTRING(@hex, n, 1) BETWEEN 'A' AND 'F' THEN ASCII(SUBSTRING(@hex, n, 1)) - 55
        WHEN SUBSTRING(@hex, n, 1) BETWEEN 'a' AND 'f' THEN ASCII(SUBSTRING(@hex, n, 1)) - 87
        ELSE 0
    END * POWER(16, LEN(@hex) - n)
) AS dec_val
FROM nums
OPTION (MAXRECURSION 0);

應用場景

• 解析十六進制編碼的色碼、雜湊值、MAC 位址。
• 舊系統資料遷移，欄位以字串儲存非十進制數值。
• 動態進制轉換（Base36、Base62 等）。