blocks|key|981582|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|981583|SELECT+CONVERT(varchar(5000),+yourvarbincolumn,+0)|code-block|syntax|javascript|981584|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this

<pre><code>SELECT CONVERT(varchar(5000), yourvarbincolumn, 0)
</code></pre>

blocks|key|1196102|text|“将varbinary转换为varchar”可能意味着不同的事情。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1196103|如果varbinary是SQL+Server中字符串的二进制表示形式(例如，通过直接转换为varbinary或从DecryptByPassPhrase或DECOMPRESS函数返回)，则只需将其转换为CAST即可|1196104|declare+@b+varbinary(max)
set+@b+=+0x5468697320697320612074657374

select+cast(@b+as+varchar(max))+/*Returns+"This+is+a+test"*/|code-block|syntax|javascript|1196105|这相当于在样式参数为0的情况下使用CONVERT。|1196106|CONVERT(varchar(max),+@b,+0)|1196107|其他样式参数可用于CONVERT，以满足其他答案中提到的不同要求。|1196108|entityMap^0|2|9|E|7|0|19|9|1K|J|24|A|2S|4|0|0|A|1|H|7|0|0|9|7|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|18|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|O|3|P|5|J|7|1E|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1F|8|@$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|T|$]]

"Converting a <code>varbinary</code> to a <code>varchar</code>" can mean different things.

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

<pre><code>declare @b varbinary(max)
set @b = 0x5468697320697320612074657374

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

This is the equivalent of using <code>CONVERT</code> with a style parameter of <code>0</code>. 

<pre><code>CONVERT(varchar(max), @b, 0)
</code></pre>

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

blocks|key|1196144|text|下面的表达式适用于我：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1196145|SELECT+CONVERT(VARCHAR(1000),+varbinary_value,+2);|code-block|syntax|javascript|1196146|Here是关于样式选择(第三个参数)的更多细节。|offset|length|1196147|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187928.aspx^0|0|0|0|4|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

The following expression worked for me:

<pre><code>SELECT CONVERT(VARCHAR(1000), varbinary_value, 2);
</code></pre>

<a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx">Here</a> are more details on the choice of style (the third parameter).

blocks|key|1196208|text|实际上最好的答案是|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1196209|SELECT+CONVERT(VARCHAR(1000),+varbinary_value,+1);|code-block|syntax|javascript|1196210|使用"2“会截断varbinary开头的"0x”。|offset|length|style|CODE|1196211|entityMap^0|0|0|3|1|8|9|L|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

Actually the best answer is 

<pre><code>SELECT CONVERT(VARCHAR(1000), varbinary_value, 1);
</code></pre>

using "<code>2</code>" cuts off the "<code>0x</code>" at the start of the <code>varbinary</code>.

blocks|key|1196274|text|对于VARBINARY(MAX)列，我必须使用NVARCHAR(MAX)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1196275|cast(Content+as+nvarchar(max))|code-block|syntax|javascript|1196276|或|1196277|CONVERT(NVARCHAR(MAX),+Content,+0)
VARCHAR(MAX)+didn't+show+the+entire+value|1196278|entityMap^0|2|E|N|D|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

For a <code>VARBINARY(MAX)</code> column, I had to use <code>NVARCHAR(MAX)</code>:

<pre><code>cast(Content as nvarchar(max))
</code></pre>

Or

<pre><code>CONVERT(NVARCHAR(MAX), Content, 0)
VARCHAR(MAX) didn't show the entire value
</code></pre>

blocks|key|981806|text|我试过了，它对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|981807|declare+@b2+VARBINARY(MAX)+
set+@b2+=+0x54006800690073002000690073002000610020007400650073007400
SELECT+CONVERT(nVARCHAR(1000),+@b2,+0);|code-block|syntax|javascript|981808|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I tried this, it worked for me:

<pre><code>declare @b2 VARBINARY(MAX) 
set @b2 = 0x54006800690073002000690073002000610020007400650073007400
SELECT CONVERT(nVARCHAR(1000), @b2, 0);
</code></pre>

blocks|key|981848|text|尝试一下下面的内容，因为我正在努力|type|unstyled|depth|inlineStyleRanges|entityRanges|data|981849|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|code-block|syntax|javascript|981850|参考：original+post|offset|length|981851|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/60525910/powershell-truncating-sql-query-output?noredirect=1#comment107077512_60525910^0|0|0|3|D|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Have a go at the below as I was struggling to
<pre><code>bcp &quot;SELECT CAST(BINARYCOL AS VARCHAR(MAX)) FROM OLTP_TABLE WHERE ID=123123 AND COMPANYID=123&quot; 
queryout &quot;C:\Users\USER\Documents\ps_scripts\res.txt&quot; -c -S myserver.db.com -U admin -P password
</code></pre>
Reference: <a href="https://stackoverflow.com/questions/60525910/powershell-truncating-sql-query-output?noredirect=1#comment107077512_60525910">original post</a>

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

varbinary to string on SQL Server

如何以人类可读的形式将列值从varbinary(max)转换为varchar？

问在SQL Server上将varbinary转换为字符串
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server上将varbinary转换为字符串EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL Server上将varbinary转换为字符串
EN