<p>I'm using SQL Server 2000 to print out some values from a table using <code>PRINT</code>.  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:</p>

<pre><code>DECLARE @binvalue binary(4)
SET @binvalue = 0x12345678
PRINT CAST(@binvalue AS nvarchar)
</code></pre>

<p>Expected:</p>

<blockquote>
  <p>0x12345678</p>
</blockquote>

<p>Instead, it prints two gibberish characters.</p>

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

<p>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.</p>

[{'answer_id': 68858, 'author': 'Ricardo C', 'author_id': 232589, 'author_profile': 'https://Stackoverflow.com/users/232589', 'pm_score': 2, 'selected': False, 'text': "<pre><code>DECLARE @binvalue binary(4)\nSET @binvalue = 0x61000000\nPRINT @binvalue \nPRINT cast('a' AS binary(4))\nPRINT cast(0x61 AS varchar)\n</code></pre>\n\n<p>Do not cast. </p>\n\n<p>Casting converts the binary to text by value on the corresponding collation setting for the specific database.</p>\n\n<p>[Begin Edit]\nIf you need the printed value in a string variable use the function suggested by Eric Z Beard.</p>\n\n<pre><code>DECLARE @mybin1 binary(16)\nDECLARE @s varchar(100)\nSET @mybin1 = 0x098F6BCD4621D373CADE4E832627B4F6\nSET @s = 'The value of @mybin1 is: ' + sys.fn_varbintohexsubstring(0, @mybin1,1,0)\nPRINT @s\n</code></pre>\n\n<p>If this function is not at your disposal due to server versions or because it needs special permissions, you can create your own function.</p>\n\n<p>To see how that function was implemented in SQL Server 2005 Express edition you can execute:</p>\n\n<pre><code>sp_helptext 'fn_varbintohexsubstring'\n</code></pre>\n"}, {'answer_id': 68872, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 6, 'selected': True, 'text': "<p>If you were on Sql Server 2005 you could use this:</p>\n\n<pre><code>print master.sys.fn_varbintohexstr(@binvalue)\n</code></pre>\n\n<p>I don't think that exists on 2000, though, so you might have to roll your own.</p>\n"}, {'answer_id': 12016386, 'author': 'Ihor B.', 'author_id': 1253118, 'author_profile': 'https://Stackoverflow.com/users/1253118', 'pm_score': 6, 'selected': False, 'text': '<blockquote>\n  <p>Do not use <code>master.sys.fn_varbintohexstr</code> - it is <em>terribly slow</em>, undocumented, unsupported, and <em>might go away in a future version of SQL Server</em>.</p>\n</blockquote>\n\n<p>If you need to convert <code>binary(16)</code> to hex char, use <code>convert</code>: </p>\n\n<pre><code>convert(char(34), @binvalue, 1)\n</code></pre>\n\n<p>Why 34? because <code>16*2 + 2 = 34</code>, that is "0x" - 2 symbols, plus 2 symbols for each char.</p>\n\n<p>We tried to make 2 queries on a table with 200000 rows:</p>\n\n<ol>\n<li><pre><code>select master.sys.fn_varbintohexstr(field)\nfrom table`\n</code></pre></li>\n<li><pre><code>select convert(char(34), field, 1)\nfrom table`\n</code></pre></li>\n</ol>\n\n<p>the first one runs 2 minutes, while second one - 4 seconds.</p>\n'}, {'answer_id': 12549880, 'author': 'Charlie Affumigato', 'author_id': 1691857, 'author_profile': 'https://Stackoverflow.com/users/1691857', 'pm_score': 5, 'selected': False, 'text': "<pre><code>select convert(varchar(max), field , 1) \nfrom table\n</code></pre>\n\n<p>By <code>using varchar(max)</code> you won't have to worry about specifying the size (kind of).</p>\n"}, {'answer_id': 17951174, 'author': 'Ian Boyd', 'author_id': 12597, 'author_profile': 'https://Stackoverflow.com/users/12597', 'pm_score': 4, 'selected': False, 'text': "<p>Adding an answer which shows another example of converting <em>binary</em> data into a hex string, and back again. </p>\n\n<p>i want to convert the highest <code>timestamp</code> value into <code>varchar</code>:</p>\n\n<pre><code>SELECT \n   CONVERT(\n      varchar(50), \n      CAST(MAX(timestamp) AS varbinary(8)), \n      1) AS LastTS\nFROM Users\n</code></pre>\n\n<p>Which returns:</p>\n\n<pre><code>LastTS\n==================\n0x000000000086862C\n</code></pre>\n\n<p><strong>Note</strong>: It's important that you use <code>CONVERT</code> to convert <code>varbinary -&gt; varchar</code>. Using <code>CAST</code> will not work: </p>\n\n<pre><code>SELECT \n   CAST(\n      CAST(MAX(timestamp) AS varbinary(8)) \n      AS varchar(50) ) AS LastTS\nFROM Users\n</code></pre>\n\n<p>will treat the binary data as <em>characters</em> rather than hex values, returning an empty string.</p>\n\n<h1>Reverse it</h1>\n\n<p>To convert the stored hex string back to a timestamp:</p>\n\n<pre><code>SELECT CAST(CONVERT(varbinary(50), '0x000000000086862C', 1) AS timestamp)\n</code></pre>\n\n<blockquote>\n  <p><strong>Note</strong>: Any code is released into the public domain. No attribution required.</p>\n</blockquote>\n"}, {'answer_id': 19399525, 'author': 'David Claughton', 'author_id': 2885723, 'author_profile': 'https://Stackoverflow.com/users/2885723', 'pm_score': 2, 'selected': False, 'text': "<p>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.</p>\n\n<p>Sadly in this version of SQL Server, CONVERT does not seem to work at all, only fn_varbintohexsubstring does the correct thing:</p>\n\n<p>I did:</p>\n\n<pre><code>DECLARE @binvalue binary(4)\nSET @binvalue = 0x12345678\nPRINT 'cast(@binvalue AS nvarchar): ' + CAST(@binvalue AS nvarchar)\nPRINT 'convert(varchar(max), @binvalue, 0): ' + CONVERT(varchar(max), @binvalue, 0)\nPRINT 'convert(varchar(max), @binvalue, 1): ' + CONVERT(varchar(max), @binvalue, 1)\nPRINT 'convert(varchar(max), @binvalue, 2): ' + CONVERT(varchar(max), @binvalue, 2)\nprint 'master.sys.fn_varbintohexstr(@binvalue): ' + master.sys.fn_varbintohexstr(@binvalue)\n</code></pre>\n\n<p>Here is the result I got  in SQL Server 2005 (</p>\n\n<pre><code>cast(@binvalue AS nvarchar): 㐒硖\nconvert(varchar(max), @binvalue, 0): 4Vx\nconvert(varchar(max), @binvalue, 1): 4Vx\nconvert(varchar(max), @binvalue, 2): 4Vx\nmaster.sys.fn_varbintohexstr(@binvalue): 0x12345678\n</code></pre>\n\n<p>(there's actually an unprintable character before the '4Vx's - I'd post an image, but I don't have enough points yet).</p>\n\n<hr>\n\n<p><strong>Edit</strong>: Just to add - on SQL Server 2008 R2 the problem with CONVERT is fixed with the following output:</p>\n\n<pre><code>cast(@binvalue AS nvarchar): 㐒硖\nconvert(varchar(max), @binvalue, 0): 4Vx\nconvert(varchar(max), @binvalue, 1): 0x12345678\nconvert(varchar(max), @binvalue, 2): 12345678\nmaster.sys.fn_varbintohexstr(@binvalue): 0x12345678\n</code></pre>\n"}, {'answer_id': 65160592, 'author': 'Nick Legend', 'author_id': 3868454, 'author_profile': 'https://Stackoverflow.com/users/3868454', 'pm_score': 0, 'selected': False, 'text': '<p>Really too much of tl;dr in the topic :( Will try to fix it following <a href="https://stackoverflow.com/a/12549880/10815638">this answer</a>.</p>\n<pre><code>with \nsq1 as (select \'41424344\' as v), -- this is \'ABCD\'\n-- Need binary size, otherwise it sets binary(30) in my case\nsq2 as (select v, convert(binary(4), v, 2) as b from sq1), \nsq3 as (select b, v, convert(varchar, b, 2) as v1 from sq2)\n--\nselect b, v, v1 from sq3\nwhere v = v1\n;\n</code></pre>\n<p>The output is:</p>\n<pre><code>b   |v       |v1      |\n----|--------|--------|\nABCD|41424344|41424344|\n</code></pre>\n<p>Also see: <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15" rel="nofollow noreferrer">documentation</a></p>\n'}]
['https://Stackoverflow.com/questions/68677', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3750/']