Technical Article

Convert Hex value to Signed 64-bit bigint

,

Convert, up to 16 nibbles, hex ('FFFFFFFFFFFFFFFF'..'0000000000000000')

to Signed 64-bit bigint (-9223372036854775808..9223372036854775807).

/* ======================================================================== *
 *   Function Name:    fnHexToBigint                                        *
 *                                                                          *
 * -- Description --------------------------------------------------------- *
 * Convert, up to 16 nibbles, hex ('FFFFFFFFFFFFFFFF'..'0000000000000000')  *
 *      to                                                                  *
 *  Signed 64-bit bigint (-9223372036854775808..9223372036854775807).       *
 * -- History ------------------------------------------------------------- *
 * 1.0.0 28.Oct.2001, Ofer Bester                                           *
 * ======================================================================== */PRINT '* CREATE FUNCTION: fnHexToBigint, Ver. 1.0.0 (28.Oct.2001).'
SETUSER 'dbo'
go

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToBigint' AND type IN ('FN', 'IF', 'TF'))
BEGIN
    PRINT '  + DROP FUNCTION dbo.fnHexToBigint.'
    DROP FUNCTION dbo.fnHexToBigint
END
PRINT '  + CREATE FUNCTION dbo.fnHexToBigint.'
go

CREATE FUNCTION dbo.fnHexToBigint ( @Hex varchar(16) )
RETURNS bigint
-- Ver. 1.0.0 28.Oct.2001, Ofer Bester
AS BEGIN
    DECLARE @i      tinyint,
            @Nibble tinyint,
            @ch     char(1),
            @Result bigint

    SET @i      = 1                                                     -- Init nibble counter
    SET @Result = 0                                                     -- Init output parameter

    SET @Hex     = UPPER( LTRIM( RTRIM( @Hex ) ) )                      -- Convert to uppercase

    WHILE (@i <= LEN(@Hex))
    BEGIN
        SET @ch = SUBSTRING(@Hex, @i, 1)

        IF      (@ch >= '0' AND @ch <= '9') SET @Nibble = ASCII(@ch) - ASCII('0')
        ELSE IF (@ch >= 'A' AND @ch <= 'F') SET @Nibble = ASCII(@ch) - ASCII('A') +10
        ELSE RETURN NULL                                                -- Invalid Hex disgit

        IF( @Result > 0x7FFFFFFFFFFFFFF)
        BEGIN
            SET @Result = @Result & 0x7FFFFFFFFFFFFFF                   -- Set MSB, of 15 nibbles, OFF
            SET @Result = @Result * 16 + @Nibble +0x8000000000000000    -- Shift left 4Bits, Add last nibble and convert to negetive number.
        END
        ELSE BEGIN
           SET @Result = @Result *16 +@Nibble                           -- Shift left 4Bits, Add nibble.
        END

        SET @i = @i +1                                                  -- Next nibble.
    END -- While

    RETURN ( @Result )
END -- Function
go

-- Check create result -------------------------------------------------------
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'fnHexToBigint' AND type IN ('FN', 'IF', 'TF'))
BEGIN
    PRINT 'DONE (CREATE FUNCTION: fnHexToBigint).'
END
ELSE BEGIN
    PRINT '****** FAILES (CREATE FUNCTION: fnHexToBigint) ******'
END
PRINT ''
go

SETUSER
go

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating