How to convert a column value from varbinary(max) to varchar in human-readable form?
-
3i want varchar since the value was isterted from string value.. I mean to read what was written..Bilgin Kılıç– Bilgin Kılıç2010-07-20 12:43:59 +00:00Commented Jul 20, 2010 at 12:43
-
2People 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 wasMartin Smith– Martin Smith2019-10-08 06:58:57 +00:00Commented Oct 8, 2019 at 6:58
Add a comment
|
8 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).
5 Comments
Stan
As described by @lara-mayugba below, style 1 includes the 0x prefix on the result which can be useful..
TheNerdyNerd
Pre 2008 use sys.fn_sqlvarbasetostr(@binaryfield)
Martin Smith
@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
Zachary Scott
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.
YungHK
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.
"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.
4 Comments
Philipp Munin
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
Martin Smith
@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
@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.TheNerdyNerd
SQL 2005 you can use sys.fn_sqlvarbasetostr(@binary) as the CONVERT will be blank for me
Actually the best answer is
SELECT CONVERT(VARCHAR(1000), varbinary_value, 1);
using "2" cuts off the "0x" at the start of the varbinary.
Comments
Try this
SELECT CONVERT(varchar(5000), yourvarbincolumn, 0)
2 Comments
sulaiman sudirman
in my case i have to use MAX instead of 5000
I tried this, it worked for me:
declare @b2 VARBINARY(MAX)
set @b2 = 0x54006800690073002000690073002000610020007400650073007400
SELECT CONVERT(nVARCHAR(1000), @b2, 0);
3 Comments
Ram
Same for me, nVARCHAR worked for me instead of VARCHAR
lovrodoe
Doesn't work on a column in my database, I'm guessing due to my default collation that's one of the European ones. Or perhaps inconsistencies between that and whatever the application that uses the database stores it in. I'm getting the output in Hanzi :-)
lovrodoe
Since I can't edit my previous comment, I just wanted to add that varchar instead of nvarchar works for me!
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
Comments
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
Comments
Replace YOURFIELD with your field. And you can omit the ISNULL part if you like.
SELECT ISNULL(CAST(CAST(YOURFIELD AS VARBINARY(MAX)) AS
NVARCHAR(MAX)),'NA') AS YOURFIELD FROM YOURTABLE
