varbinary to string on SQL Server

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

3

7 Answers

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

6

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

5

Actually the best answer is

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

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

3

Try this

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

2

I tried this, it worked for me:

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

0

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

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy