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

Comments (5)

  1. cppprogrammer@live.com says:

    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?

  2. cppprogrammer@live.com says:

    Sorry I was connected from a SSMS 2k8 to a SQL Server 2005 server.

  3. Hasham Niaz says:

    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

Skip to main content