Convert HEX to BIGINT

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

Credits:

Author: Various (incl. Pinal Dave and the original 2000/5 from PDreyer

Date: 9 Jul 2008

Description

When I went to publish this tip, I looked at the code i had stored and something didn't seem quite right. It had become corrupted over the years. I searched out a much better function-based version which is published below and is valid for all versions of SQL Server.

However, from SQL 2008 onwards, the CAST/CONVERT functions are able to perform this conversion directly, with the HEX represented as either a VARBINARY token, or a HEX character string with or without the '0x' prefix.

Code

-- FOR SQL 2000/2005 (and 2008/12 if required)

CREATE FUNCTION dbo.fn_Hex2BigInt(@s VARCHAR(16)) --Convert hex to bigint

RETURNS BIGINT -- e.g. select dbo.fn_Hex2BigInt('7ff2a5')

AS

BEGIN

DECLARE

@i INT,

@len INT,

@c CHAR(1),

@result BIGINT

SELECT @s=UPPER(@s)

SELECT

@len=LEN(@s),

@i=@len,

@result= CASE

WHEN @len>0 THEN 0

END

WHILE (@i>0)

BEGIN

SELECT

@c=SUBSTRING(@s,@i,1),

@result=@result

+(ASCII(@c)

-( CASE

WHEN @c BETWEEN 'A' AND 'F' THEN 55

ELSE CASE

WHEN @c BETWEEN '0' AND '9' THEN 48

END

)) * POWER(16.,@len-@i),

@i=@i-1

END -- while

RETURN @result

END -- function

-- for SQL 2008 , 2008R2, 2012

DECLARE @s VARCHAR(16)

--for a hex string that has no '0x' prefix

SELECT @s = '7ff2a5'

SELECT CAST(CONVERT(VARBINARY, @s, 2) AS BIGINT)

--for a hex string that has an '0x' prefix

SELECT @s = '0x7ff2a5'

SELECT CAST(CONVERT(VARBINARY, @s, 1) AS BIGINT)