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')
