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:

https://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:

https://msdn.microsoft.com/en-us/library/ms187928(SQL.100).aspx

Comments

• Anonymous
  July 02, 2008
  PingBack from http://blog.a-foton.ru/2008/07/converting-from-hex-string-to-varbinary-and-vice-versa/

• Anonymous
  November 16, 2008
  Hi, I am running SQL Server 2008 Enterprise Evaluation with Cumulative Update 1 installed. When I process your sample in Query Analyzer, my results for the select queries are: 0x3078616263656466303132343339 and 0x616263656466303132343339 so it has converted the characters to the binary representation of the ASCII rather than what I expected: 0xABCEDF012439 Is there a flaw with CONVERT now? Or is that expected behaviour?

• Anonymous
  November 16, 2008
  Sorry I was connected from a SSMS 2k8 to a SQL Server 2005 server.

• Anonymous
  December 04, 2011
  Nice article i was just looking for a clarity how to be sure to convert VARCHAT to VARBINARY and vice versa. This article really helps me out. Thanks, Hasham

• Anonymous
  June 07, 2013
  Good one :)