varbinary to string on SQL Server

Question

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

Languet

i want varchar since the value was isterted from string value.. I mean to read what was written.. – Mohr

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 – Cyrano

Answer 1

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

Cyrano

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 – Carillo

@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. – Cyrano

@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. – Cyrano

SQL 2005 you can use sys.fn_sqlvarbasetostr(@binary) as the CONVERT will be blank for me – Hygrometer

Answer 2

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

Voelker

As described by @lara-mayugba below, style 1 includes the 0x prefix on the result which can be useful.. – Hydroid

Pre 2008 use sys.fn_sqlvarbasetostr(@binaryfield) – Hygrometer

@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 – Cyrano

To emphasize @Hygrometer '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. – Upmost

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. – Boil

Answer 3

Actually the best answer is

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

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

Genus

Answer 4

Try this

SELECT CONVERT(varchar(5000), yourvarbincolumn, 0)

Cranach

I had to use a 2 as my third parameter, instead of a zero. I found that answer here. – Hideandseek

in my case i have to use MAX instead of 5000 – Vitale

Answer 5

I tried this, it worked for me:

declare @b2 VARBINARY(MAX) 
set @b2 = 0x54006800690073002000690073002000610020007400650073007400
SELECT CONVERT(nVARCHAR(1000), @b2, 0);

Brazenfaced

Same for me, nVARCHAR worked for me instead of VARCHAR – Lymph

Answer 6

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

Christmann

Answer 7

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

Spiv