sqlsql-serversql-server-2005t-sql

varbinary to string on SQL Server

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

Solution

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