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

Tadmas_3750 0
Viewed 46987

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.

tsql sql-server
7 Answers

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.

Eric_Z_Beard_12190 
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'
user_2325890

Really too much of tl;dr in the topic :( Will try to fix it following this answer.

with 
sq1 as (select '41424344' as v), -- this is 'ABCD'
-- Need binary size, otherwise it sets binary(30) in my case
sq2 as (select v, convert(binary(4), v, 2) as b from sq1), 
sq3 as (select b, v, convert(varchar, b, 2) as v1 from sq2)
--
select b, v, v1 from sq3
where v = v1
;

The output is:

b   |v       |v1      |
----|--------|--------|
ABCD|41424344|41424344|

Also see: documentation

user_38684540
Related