6

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

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30
user1617237
  • 143
  • 1
  • 3
  • 11

2 Answers2

0

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.

ErikE
  • 48,881
  • 23
  • 151
  • 196
-1

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

user1617237
  • 143
  • 1
  • 3
  • 11
  • 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. – ErikE Oct 12 '16 at 22:09