132

How to convert a column value from varbinary(max) to varchar in human-readable form?

Mike G
  • 4,232
  • 9
  • 40
  • 66
Bilgin Kılıç
  • 8,707
  • 14
  • 41
  • 67
  • 3
    i want varchar since the value was isterted from string value.. I mean to read what was written.. – Bilgin Kılıç Jul 20 '10 at 12:43
  • 2
    People seem to be coming across this from search engines and based on the voting the style 2 parameter seems more commonly required but this does not do what your original requirement was – Martin Smith Oct 08 '19 at 06:58

7 Answers7

183

The following expression worked for me:

SELECT CONVERT(VARCHAR(1000), varbinary_value, 2);

Here are more details on the choice of style (the third parameter).

Gunjan Juyal
  • 1,939
  • 1
  • 12
  • 3
  • 4
    As described by @lara-mayugba below, style 1 includes the 0x prefix on the result which can be useful.. – Stan Nov 09 '17 at 16:07
  • 1
    Pre 2008 use sys.fn_sqlvarbasetostr(@binaryfield) – TheNerdyNerd Mar 03 '18 at 15:32
  • @metabuddy - in what way is it misinformation? It states that the term "Converting a varbinary to a varchar" can be done in different ways and that these are controlled by the style parameter. Just because the style parameter in that answer isn't the one you need for your case (but is the one that answers the original question) doesn't make it misinformation – Martin Smith Oct 08 '19 at 06:56
  • 1
    To emphasize @TheNerdyNerd 's comment / answer, the expression would be `select sys.fn_sqlvarbasetostr(@b) /* returns 0x5468697320697320612074657374 */ Assuming one changes varchar(max) to varchar(8000) because pre-2008 doesn't use it. – Zachary Scott Jun 18 '20 at 19:13
  • I think what @Bilgin Kılıç was trying to achieve was he wanted to decode the content to varchar, previously coded from varchar to varbinary. Using 2 as style should produce a different answer. On the other hand, using 0 emulate the answer in this context. – YungHK Jun 02 '21 at 16:31
120

"Converting a varbinary to a varchar" can mean different things.

If the varbinary is the binary representation of a string in SQL Server (for example returned by casting to varbinary directly or from the DecryptByPassPhrase or DECOMPRESS functions) you can just CAST it

declare @b varbinary(max)
set @b = 0x5468697320697320612074657374

select cast(@b as varchar(max)) /*Returns "This is a test"*/

This is the equivalent of using CONVERT with a style parameter of 0. 

CONVERT(varchar(max), @b, 0)

Other style parameters are available with CONVERT for different requirements as noted in other answers.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 44
    THIS ANSWER IS NOT CORRECT. I tested it on table with user SIDs - amount of Distinct casted values is less then amount of Distinct binary SIDs. You should use CONVERT(VARCHAR(...), binaryValue, 2) to get unique value - answer from Gunjan Juyal is the right one - it should be marked as solution – Philipp Munin Oct 18 '13 at 20:03
  • 16
    @PhilippMunin - The two answers do different things. This one takes the result of an expression such as `SELECT CAST('This is a test' AS VARBINARY(100))` which is `0x5468697320697320612074657374` in my default collation and converts it back to the `varchar` string. Gunjan's answer returns the hex representation as a string ('5468697320697320612074657374') Presumably this interpretation is correct for the OP's need as they accepted it. – Martin Smith Oct 18 '13 at 20:28
  • 2
    @BIDeveloper if you had read the comments above (specifically my one) you should realise that the issue is that "converting varbinary to varchar" can be interpreted in different ways. Indeed this is why `CONVERT` has a style parameter to select the way you want (my interpretation is the default style) So this answer may not be what you need for your use case at the moment but it is correct for other use cases. Including the original questioner's who specified "human readable form" not hex. – Martin Smith Jun 17 '16 at 11:41
  • 1
    SQL 2005 you can use sys.fn_sqlvarbasetostr(@binary) as the CONVERT will be blank for me – TheNerdyNerd Mar 03 '18 at 15:36
82

Actually the best answer is 

SELECT CONVERT(VARCHAR(1000), varbinary_value, 1);

using "2" cuts off the "0x" at the start of the varbinary.

Praveen
  • 8,945
  • 4
  • 31
  • 49
Lara Mayugba
  • 821
  • 6
  • 2
16

Try this

SELECT CONVERT(varchar(5000), yourvarbincolumn, 0)
dmajkic
  • 3,448
  • 1
  • 18
  • 24
  • 7
    I had to use a 2 as my third parameter, instead of a zero. I found that answer [here](http://stackoverflow.com/questions/12139073/sql-server-converting-varbinary-to-string). – WEFX Jan 02 '13 at 16:10
  • 1
    in my case i have to use MAX instead of 5000 – sulaiman sudirman Feb 01 '18 at 02:40
6

I tried this, it worked for me:

declare @b2 VARBINARY(MAX) 
set @b2 = 0x54006800690073002000690073002000610020007400650073007400
SELECT CONVERT(nVARCHAR(1000), @b2, 0);
sɐunıɔןɐqɐp
  • 3,332
  • 15
  • 36
  • 40
Bala
  • 125
  • 1
  • 2
  • 12
3

For a VARBINARY(MAX) column, I had to use NVARCHAR(MAX):

cast(Content as nvarchar(max))

Or

CONVERT(NVARCHAR(MAX), Content, 0)
VARCHAR(MAX) didn't show the entire value
sɐunıɔןɐqɐp
  • 3,332
  • 15
  • 36
  • 40
kristoffer_o
  • 590
  • 5
  • 7
3

Have a go at the below as I was struggling to

bcp "SELECT CAST(BINARYCOL AS VARCHAR(MAX)) FROM OLTP_TABLE WHERE ID=123123 AND COMPANYID=123" 
queryout "C:\Users\USER\Documents\ps_scripts\res.txt" -c -S myserver.db.com  -U admin -P password

Reference: original post

pix
  • 1,264
  • 19
  • 32
ribbit
  • 79
  • 2
  • 13