Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Converting from hex string to varbinary and vice versa

Posted by Simon Cho on 01/31/2012

http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

SQL2005

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

SQL2008

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
Advertisements

One Response to “Converting from hex string to varbinary and vice versa”

  1. Thorkil Johansen said

    After searching for a charhex to int for hours, you gave me the inspiration.
    Thanx trom Thorkil
    Copenhagen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: