Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - 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
                   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)



ċ
Convert Hext to BigInt.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments