Wednesday, July 4, 2012

Conversion from Hex String to VarBinary and vice versa

-- Conversion from hex string 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)

-- Conversion from varbinary to hex string: 

DECLARE @hexbin VarBinary(MAX); 
SET @hexbin = 0xabcedf012439; 
SELECT '0x' + CAST('' AS XML).Value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)'); 

No comments:

Post a Comment