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

DECLARE @binvalue binary(4)
SET @binvalue = 0x61000000
PRINT @binvalue 
PRINT cast('a' AS binary(4))
PRINT cast(0x61 AS varchar)

Do not cast.

Casting converts the binary to text by value on the corresponding collation setting for the specific database.

[Begin Edit] If you need the printed value in a string variable use the function suggested by Eric Z Beard.

DECLARE @mybin1 binary(16)
DECLARE @s varchar(100)
SET @mybin1 = 0x098F6BCD4621D373CADE4E832627B4F6
SET @s = 'The value of @mybin1 is: ' + sys.fn_varbintohexsubstring(0, @mybin1,1,0)
PRINT @s

If this function is not at your disposal due to server versions or because it needs special permissions, you can create your own function.

To see how that function was implemented in SQL Server 2005 Express edition you can execute:

sp_helptext 'fn_varbintohexsubstring'

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