SQL server convert hex string to varbinary

Question

I have a string column that represents hex values, for example -
'274', '1A7', '3D1' and so on.

Now I need to convert these values to their integer values, so that '10' will be converted to 16, for example.

The code I use:

SELECT CONVERT(int, CONVERT(varbinary, '0x' + case when replicate('0', len(myHex) / 2) +     myHex = '0' then '00' else replicate('0', len(myHex) / 2) + myHex end, 1)) 

I'm actually padding the string with a zero or two to make it's length even, and adding the '0x' prefix. However some (random) rows fail. Is there another way to convert the values?

Thanks.

Answer 1

please give feedback so that i can improve my answer Here is one way to do it:

//create function fn_HexToIntnt(@str varchar(16))
//returns bigint as begin

select @str=upper(@str)
declare @i int, @len int, @char char(1), @output bigint
select @len=len(@str)
,@i=@len
,@output=case
when @len>0
then 0
end
while (@i>0)
begin
select @char=substring(@str,@i,1), @output=@output
+(ASCII(@char)
-(case
when @char between ‘A’ and ‘F’
then 55
else
case
when @char between ’0′ and ’9′
then 48 end
end))
*power(16.,@len-@i)
,@i=@i-1
end
return @output
end

or

SELECT CONVERT(INT, 0×00000100)
SELECT CONVERT(VARBINARY(8), 256)