SQL Server 十六進位與十進位互轉函數：hextoint 與 inttohex

hextoint：十六進位字串 → BIGINT

CREATE FUNCTION dbo.hextoint(@h VARCHAR(8))
RETURNS BIGINT
AS
BEGIN
    DECLARE @r BIGINT = 0, @i BIGINT = 1;
    WHILE @i <= LEN(@h)
    BEGIN
        DECLARE @ch CHAR(1) = SUBSTRING(@h, @i, 1);
        DECLARE @val INT;
        IF @ch BETWEEN '0' AND '9'       SET @val = CAST(@ch AS INT);
        ELSE IF @ch BETWEEN 'A' AND 'F'  SET @val = ASCII(@ch) - ASCII('A') + 10;
        ELSE IF @ch BETWEEN 'a' AND 'f'  SET @val = ASCII(@ch) - ASCII('a') + 10;
        ELSE RETURN NULL;  -- 非法字元

        SET @r = @r + @val * POWER(16, LEN(@h) - @i);
        SET @i = @i + 1;
    END
    RETURN @r;
END
GO

inttohex：INT → 十六進位字串 (VARCHAR)

CREATE FUNCTION dbo.inttohex(@i INT)
RETURNS VARCHAR(15)
AS
BEGIN
    IF @i = 0 RETURN '0';
    DECLARE @r VARCHAR(15) = '', @hex CHAR(16) = '0123456789ABCDEF';
    DECLARE @n INT = @i;
    WHILE @n > 0
    BEGIN
        SET @r = SUBSTRING(@hex, (@n % 16) + 1, 1) + @r;
        SET @n = @n / 16;
    END
    RETURN @r;
END
GO

使用範例

SELECT dbo.hextoint('FF');        -- 255
SELECT dbo.hextoint('1A3F');      -- 6719
SELECT dbo.inttohex(255);         -- 'FF'
SELECT dbo.inttohex(6719);        -- '1A3F'

補充說明

• hextoint 支援最多 8 位十六進位字元（對應 32 位無號整數），傳回 BIGINT 避免溢位。
• inttohex 處理正整數；若需負數，可先轉無號再處理。
• SQL Server 2008+ 可直接使用 CONVERT(VARBINARY, @int) 與 CONVERT(INT, @varbinary) 進行二進位轉換，但本函數適用於純字串十六進位格式。