Thursday, March 4, 2010

Convert hexadecimal values stored in string to integer equivalent

There have been a few occasions which  I have come across where its required to  convert hexadecimal value stored in string  value to integer. In such cases a simple cast  or convert wont work.Even converting to  intermediate varbinary will also return  wrong value. To handle such cases I've  written a UDF which I'm posting here
CREATE FUNCTION HexStrToInt  
(  @HexVal varchar (30)  )  
RETURNS int  AS  
BEGIN 
DECLARE @IntVal int  
;With Number_CTE(number)
AS
(SELECT 1
UNION ALL
SELECT number+1
FROM Number_CTE
WHERE number+1<=LEN(@HexVal)
)
SELECT  @HexVal=REPLACE(@HexVal,'0x','')  
select @IntVal= sum(case lower( substring( reverse(@HexVal), number , 1 ) )  
when '0' then 0  
when '1' then 1  
when '2' then 2  
when '3' then 3  
when '4' then 4  
when '5' then 5  
when '6' then 6  
when '7' then 7  
when '8' then 8  
when '9' then 9  
when 'a' then 10  
when 'b' then 11  
when 'c' then 12  
when 'd' then 13  
when 'e' then 14  
when 'f' then 15       
end *  power( cast(16 as bigint), number - 1 ) ) 
from Number_CTE
OPTION (MAXRECURSION 0)
RETURN @IntVal 
END   
and use it like   

select dbo.HexStrToInt('0x0000000000000028')