Is there a way to turn sys.fn_varbintohexstr result back to varbinary?

Question

Is there a function in SQL Server to do that? to reverse sys.fn_varbintohexstr?

Dasheen

Answer 1

You need to use the hexadecimal character string in a dynamic SQL statement, so that it will be parsed as a varbinary. Here's one example of how to do that.

-- Our original and fn_varbintohexstr values:
DECLARE @original varbinary(max) = 0xd0cf11;
DECLARE @sql nvarchar(max) = N'SET @converted = ' + sys.fn_varbintohexstr(@original) + ';';

-- Do the conversion
DECLARE @converted varbinary(max);
EXEC sp_executesql @sql,
   N'@converted varbinary(max) OUTPUT',
   @converted = @converted OUTPUT;

-- Proof it worked
PRINT @original;
PRINT @converted;
PRINT CASE WHEN @original = @converted THEN 'Same' ELSE 'Different' END;

This prints Same.

If you try user1617237's version, you'll see why it is not a correct answer.

Defensive

Answer 2

select convert(varbinary（max),@hex,2) from foobar

Dasheen

This doesn't work. You do get varbinary out of it, but it's the varbinary of the string representation of the original varbinary. It's not the original varbinary. – Defensive