CODEWITHSUNDEEP
sql-servert-sql
Tadmas
Tadmas

Reputation: 6358

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

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.

Upvotes: 42

Views: 52940

Answers (7)

Ihor B.
Ihor B.

Reputation: 1363

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.

Avoid master.sys.fn_varbintohexstr because it is terribly slow, undocumented, unsupported, and might go away in a future version of SQL Server.

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 in 2 minutes, while the second one runs in 4 seconds.

Upvotes: 50

Nick Legend
Nick Legend

Reputation: 1038

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

Upvotes: 0

Charlie Affumigato
Charlie Affumigato

Reputation: 1027

select convert(varchar(max), field , 1) 
from table

By using varchar(max) you won't have to worry about specifying the size (kind of).

Upvotes: 25

Ian Boyd
Ian Boyd

Reputation: 256641

Adding an answer which shows another example of converting binary data into a hex string, and back again.

i want to convert the highest timestamp value into varchar:

SELECT 
   CONVERT(
      varchar(50), 
      CAST(MAX(timestamp) AS varbinary(8)), 
      1) AS LastTS
FROM Users

Which returns:

LastTS
==================
0x000000000086862C

Note: It's important that you use CONVERT to convert varbinary -> varchar. Using CAST will not work: 

SELECT 
   CAST(
      CAST(MAX(timestamp) AS varbinary(8)) 
      AS varchar(50) ) AS LastTS
FROM Users

will treat the binary data as characters rather than hex values, returning an empty string.

Reverse it

To convert the stored hex string back to a timestamp:

SELECT CAST(CONVERT(varbinary(50), '0x000000000086862C', 1) AS timestamp)

Note: Any code is released into the public domain. No attribution required.

Upvotes: 11

David Claughton
David Claughton

Reputation: 79

I came across this question while looking for a solution to a similar problem while printing the hex value returned from the 'hashbytes' function in SQL Server 2005.

Sadly in this version of SQL Server, CONVERT does not seem to work at all, only fn_varbintohexsubstring does the correct thing:

I did:

DECLARE @binvalue binary(4)
SET @binvalue = 0x12345678
PRINT 'cast(@binvalue AS nvarchar): ' + CAST(@binvalue AS nvarchar)
PRINT 'convert(varchar(max), @binvalue, 0): ' + CONVERT(varchar(max), @binvalue, 0)
PRINT 'convert(varchar(max), @binvalue, 1): ' + CONVERT(varchar(max), @binvalue, 1)
PRINT 'convert(varchar(max), @binvalue, 2): ' + CONVERT(varchar(max), @binvalue, 2)
print 'master.sys.fn_varbintohexstr(@binvalue): ' + master.sys.fn_varbintohexstr(@binvalue)

Here is the result I got in SQL Server 2005 (

cast(@binvalue AS nvarchar): 㐒硖
convert(varchar(max), @binvalue, 0): 4Vx
convert(varchar(max), @binvalue, 1): 4Vx
convert(varchar(max), @binvalue, 2): 4Vx
master.sys.fn_varbintohexstr(@binvalue): 0x12345678

(there's actually an unprintable character before the '4Vx's - I'd post an image, but I don't have enough points yet).

Edit: Just to add - on SQL Server 2008 R2 the problem with CONVERT is fixed with the following output:

cast(@binvalue AS nvarchar): 㐒硖
convert(varchar(max), @binvalue, 0): 4Vx
convert(varchar(max), @binvalue, 1): 0x12345678
convert(varchar(max), @binvalue, 2): 12345678
master.sys.fn_varbintohexstr(@binvalue): 0x12345678

Upvotes: 2

Eric Z Beard
Eric Z Beard

Reputation: 38406

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.

Upvotes: 29

Ricardo C
Ricardo C

Reputation: 2244

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'

Upvotes: 2

Related Questions