How to convert a column value from varbinary(max)
to varchar
in human-readable form?
This question is related to
sql
sql-server
sql-server-2005
tsql
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
I tried this, it worked for me:
declare @b2 VARBINARY(MAX)
set @b2 = 0x54006800690073002000690073002000610020007400650073007400
SELECT CONVERT(nVARCHAR(1000), @b2, 0);
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).
Try this
SELECT CONVERT(varchar(5000), yourvarbincolumn, 0)
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
Actually the best answer is
SELECT CONVERT(VARCHAR(1000), varbinary_value, 1);
using "2
" cuts off the "0x
" at the start of the varbinary
.
Source: Stackoverflow.com