Converting hex to varbinary and vice versa

 

HexString To Varbinary :

CREATE FUNCTION [dbo].[fnHexStrToVarbinary]
(
@inputText varchar(max)
)
returns varbinary(max)
as
begin

declare @binvalue varbinary(8000), @sqlstring Nvarchar(1000)

--X Query

select @binvalue = cast('' as xml).value('xs:hexBinary( substring(sql:variable("@inputText"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@inputText, 1, 2) when '0x' then 3 else 0 end) as t(pos)

RETURN @binvalue

end

Testing :

declare @hexstring varchar(MAX)
select @hexstring = ‘0x001be96ccd14ef47acbf24583b972c250100000031071af34c3e9aac1346d62c8507f0ab9255c827adcf82fad7029c1ee844fcfd1d3b89cf6e89afcda812441be1c9a5b5’

select dbo.[fnHexStrToVarbinary](@hexstring)

 

Varbinary to Hex string :

declare @binary varbinary(MAX)
select @binary = 0x001be96ccd14ef47acbf24583b972c250100000031071af34c3e9aac1346d62c8507f0ab9255c827adcf82fad7029c1ee844fcfd1d3b89cf6e89afcda812441be1c9a5b5

select master.dbo.fn_varbintohexstr(binary) --Built in function

So altogether we can convert one from another like this..

declare @hexstring varchar(MAX)

declare @binary varbinary(MAX)
select @binary = 0x001BE96CCD14EF47ACBF24583B972C250100000031071AF34C3E9AAC1346D62C8507F0AB9255C827ADCF82FAD7029C1EE844FCFD1D3B89CF6E89AFCDA812441BE1C9A5B5 – must not include in ‘’ as it is a varbinary field.

select  @hexstring = master.dbo.fn_varbintohexstr(@binary)

print @hexstring
select dbo.[fnHexStrToVarbinary](@hexstring)

select case when dbo.[fnHexStrToVarbinary](@hexstring) = @binary then ‘Same Values’ else ‘Different Values’
end