SQL Server 十六進制與十進制互轉：VARBINARY 中轉法解決 64 位無符號長整數存儲難題

問題背景

工控項目中 64 位無符號整數 (0~2^64-1) 需在 SQL Server 存儲。`BIGINT` 為有符號 (-2^63~2^63-1)，超範圍正數無法直接存儲，負數十六進制轉十進制更棘手。

核心思路：VARBINARY 做中介

`VARBINARY(8)` 精確對應 8 字節 = 64 位。利用 `CONVERT` 樣式 1/2 實現十六進制字符串 ↔ VARBINARY ↔ 十進制 BIGINT 雙向轉換。

三大核心函數

1. 十六進制字符串 → 十進制 BIGINT (含負數/無符號大值)

CREATE FUNCTION dbo.HexToBigInt(@HexStr VARCHAR(16))
RETURNS BIGINT
AS BEGIN
    -- 補齊 16 字符、補 0x 前綴、轉 VARBINARY 再轉 BIGINT
    DECLARE @Bin VARBINARY(8) = CONVERT(VARBINARY(8), '0x' + RIGHT('0000000000000000' + @HexStr, 16), 1);
    RETURN CONVERT(BIGINT, @Bin);
END

2. 十進制 BIGINT (含負數) → 十六進制字符串

CREATE FUNCTION dbo.BigIntToHex(@Num BIGINT)
RETURNS VARCHAR(16)
AS BEGIN
    DECLARE @Bin VARBINARY(8) = CONVERT(VARBINARY(8), @Num);
    RETURN dbo.VarBinToHexStr(@Bin);
END

3. VARBINARY → 十六進制字符串 (通用、高性能)

CREATE FUNCTION dbo.VarBinToHexStr(@Bin VARBINARY(8000))
RETURNS VARCHAR(8000)
AS BEGIN
    DECLARE @Hex CHAR(16) = '0123456789ABCDEF', @Result VARCHAR(8000) = '', @Len INT = DATALENGTH(@Bin);
    WHILE @Len > 0
    BEGIN
        SET @Result = SUBSTRING(@Hex, (CAST(SUBSTRING(@Bin, @Len, 1) AS INT) / 16) + 1, 1) +
                      SUBSTRING(@Hex, (CAST(SUBSTRING(@Bin, @Len, 1) AS INT) % 16) + 1, 1) + @Result;
        SET @Len -= 1;
    END
    RETURN @Result;
END

測試驗證

應用場景

• 工控設備 64 位計數器數值存儲/查詢
• 網絡設備 MAC/IPv6 地址十六進制存儲
• 加密雜湊值 (MD5/SHA 截斷) 存儲與比對
• 異構系統數據交換格式轉換

現代替代 (SQL 2008+)

-- 直接用 CONVERT 樣式 1/2
SELECT CONVERT(BIGINT, CONVERT(VARBINARY(8), '0xFFFFFFFFFFFFFFFF', 1)); -- -1
SELECT CONVERT(VARBINARY(8), -1, 2); -- 0xFFFFFFFFFFFFFFFF

但自定義函數封裝邏輯、支援可變長度、統一介面仍有工程價值。