varbinary to string on SQL Server
"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.
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).