How can I print a binary value as hex in TSQL?

Question

I'm using SQL Server 2000 to print out some values from a table using PRINT. With most non-string data, I can cast to nvarchar to be able to print it, but binary values attempt to convert using the bit representation of characters. For example:

DECLARE @binvalue binary(4)
SET @binvalue = 0x12345678
PRINT CAST(@binvalue AS nvarchar)

Expected:

0x12345678

Instead, it prints two gibberish characters.

How can I print the value of binary data? Is there a built-in or do I need to roll my own?

Update: This isn't the only value on the line, so I can't just PRINT @binvalue. It's something more like PRINT N'other stuff' + ???? + N'more stuff'. Not sure if that makes a difference: I didn't try just PRINT @binvalue by itself.

Answer 1

Do not use master.sys.fn_varbintohexstr - it is terribly slow, undocumented, unsupported, and might go away in a future version of SQL Server.

If you need to convert binary(16) to hex char, use convert:

convert(char(34), @binvalue, 1)

Why 34? because 16*2 + 2 = 34, that is "0x" - 2 symbols, plus 2 symbols for each char.

We tried to make 2 queries on a table with 200000 rows:

1. select master.sys.fn_varbintohexstr(field)
   from table`
   

2. select convert(char(34), field, 1)
   from table`
   

the first one runs 2 minutes, while second one - 4 seconds.

Answer 2

If you were on Sql Server 2005 you could use this:

print master.sys.fn_varbintohexstr(@binvalue)

I don't think that exists on 2000, though, so you might have to roll your own.