Converting hexadecimal values to varbinary and vice versa is now easier using the XQuery functionality available from SQL Server 2005. The code samples below show how to perform the conversion(s):
-- Convert hexstring value in a variable to varbinary:
declare @hexstring varchar(max);
set @hexstring = 'abcedf012439';
select cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)
go
-- Convert binary value in a variable to hexstring:
declare @hexbin varbinary(max);
set @hexbin = 0xabcedf012439;
select '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)');
For more details on XQuery see link below:
http://msdn.microsoft.com/en-us/library/ms189075(SQL.100).aspx
In SQL Server 2008, these conversions are even more easier since we added support directly in the CONVERT built-in function. The code samples below show how to perform the conversion(s):
set @hexstring = '0xabcedf012439';
select CONVERT(varbinary(max), @hexstring, 1);
select CONVERT(varbinary(max), @hexstring, 2);
select CONVERT(varchar(max), @hexbin, 1), CONVERT(varchar(max), @hexbin, 2);
For more details on the new CONVERT binary styles see link below:
http://msdn.microsoft.com/en-us/library/ms187928(SQL.100).aspx