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.

user2881430 · Accepted Answer · 2013-10-17 07:28:57Z

3

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)

edited Oct 17, 2013 at 7:28

answered Oct 17, 2013 at 7:22

user2881430

3671 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2881430 Over a year ago

this is the way to convert hex to dec

Collectives™ on Stack Overflow

SQL server convert hex string to varbinary

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related