次の方法で共有

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 :)