Converting from hex string to varbinary and vice versa
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)');
go
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):
declare @hexstring varchar(max);
set @hexstring = '0xabcedf012439';
select CONVERT(varbinary(max), @hexstring, 1);
set @hexstring = 'abcedf012439';
select CONVERT(varbinary(max), @hexstring, 2);
go
declare @hexbin varbinary(max);
set @hexbin = 0xabcedf012439;
select CONVERT(varchar(max), @hexbin, 1), CONVERT(varchar(max), @hexbin, 2);
go
For more details on the new CONVERT binary styles see link below:
http://msdn.microsoft.com/en-us/library/ms187928(SQL.100).aspx
