SQL Server 十六進制與十進制互轉自訂函數

前言

SQL Server 內建 CONVERT 可處理二進位與字串轉換，但無直接的十六進制字串轉整數函數。本文提供兩個自訂純量函數：dbo.HexToInt（十六進制字串轉 BIGINT）與 dbo.IntToHex（整數轉十六進制字串），採用集合導向邏輯，效能優於迴圈。

函數一：HexToInt - 十六進制字串轉十進制整數

CREATE FUNCTION dbo.HexToInt (@hex VARCHAR(16))
RETURNS BIGINT
AS
BEGIN
    DECLARE @result BIGINT = 0;
    DECLARE @i INT = 1;
    DECLARE @len INT = LEN(@hex);
    DECLARE @char CHAR(1);
    DECLARE @val INT;

    -- 移除可能的 0x 前置詞
    IF @hex LIKE '0x%' SET @hex = SUBSTRING(@hex, 3, @len - 2);

    WHILE @i <= LEN(@hex)
    BEGIN
        SET @char = SUBSTRING(@hex, @i, 1);
        SET @val = CASE
            WHEN @char BETWEEN '0' AND '9' THEN ASCII(@char) - 48
            WHEN @char BETWEEN 'A' AND 'F' THEN ASCII(@char) - 55
            WHEN @char BETWEEN 'a' AND 'f' THEN ASCII(@char) - 87
            ELSE 0
        END;
        SET @result = @result * 16 + @val;
        SET @i = @i + 1;
    END
    RETURN @result;
END;
GO

函數二：IntToHex - 十進制整數轉十六進制字串

CREATE FUNCTION dbo.IntToHex (@num BIGINT)
RETURNS VARCHAR(16)
AS
BEGIN
    DECLARE @hex VARCHAR(16) = '';
    DECLARE @digits CHAR(16) = '0123456789ABCDEF';
    IF @num = 0 RETURN '0';
    WHILE @num > 0
    BEGIN
        SET @hex = SUBSTRING(@digits, (@num % 16) + 1, 1) + @hex;
        SET @num = @num / 16;
    END
    RETURN @hex;
END;
GO

測試範例

SELECT dbo.HexToInt('2D')      AS HexToInt_Result;  -- 45
SELECT dbo.HexToInt('0x2D')    AS HexToInt_WithPrefix; -- 45
SELECT dbo.IntToHex(45)        AS IntToHex_Result;  -- '2D'
SELECT dbo.IntToHex(255)       AS IntToHex_FF;      -- 'FF'

效能優化版（數字表法，適用大量資料）

CREATE FUNCTION dbo.HexToInt_Fast (@hex VARCHAR(16))
RETURNS BIGINT
AS
BEGIN
    DECLARE @cleanHex VARCHAR(16) = REPLACE(REPLACE(@hex, '0x', ''), '0X', '');
    RETURN (
        SELECT SUM(
            POWER(16, LEN(@cleanHex) - n) *
            CASE SUBSTRING(@cleanHex, n, 1)
                WHEN '0' THEN 0 WHEN '1' THEN 1 WHEN '2' THEN 2 WHEN '3' THEN 3
                WHEN '4' THEN 4 WHEN '5' THEN 5 WHEN '6' THEN 6 WHEN '7' THEN 7
                WHEN '8' THEN 8 WHEN '9' THEN 9
                WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12 WHEN 'D' THEN 13
                WHEN 'E' THEN 14 WHEN 'F' THEN 15
                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 0 END
        )
        FROM dbo.TallyTable -- 需預建 1..16 的數字表
        WHERE n <= LEN(@cleanHex)
    );
END;

註：生產環境建議建立永久數字表 dbo.TallyTable(n INT PRIMARY KEY) 以避免遞迴 CTE 產生序列的開銷。

應用場景

• 解析二進位資料欄位（如 varbinary 轉十六進制字串後再轉數值）。
• 與舊系統整合，需處理十六進制編碼的 ID、顏色代碼、雜湊值等。