Convert Binary to String in SQL Server

I recently found an undocumented function in the Master database to convert Binary to String in SQL Server. It’s called fn_sqlvarbasetostr and the code for this function is as shown below:

create function sys.fn_sqlvarbasetostr (
@ssvar sql_variant
)
returns nvarchar(max)
as
begin
declare @pstrout nvarchar(max)
,@basetype sysname
select @basetype = lower(cast(SQL_VARIANT_PROPERTY ( @ssvar, 'BaseType' ) as sysname) collate database_default)
if (@ssvar IS NOT NULL and @basetype IS NOT NULL)
begin
if (@basetype = N'varchar')
select @pstrout = N'''' + REPLACE(CAST(@ssvar as nvarchar(max)), '''', '''''') + N''''
else if (@basetype in (N'nvarchar', N'xml'))
select @pstrout = N'N''' + REPLACE(CAST(@ssvar as nvarchar(max)), '''', '''''') + N''''
else if (@basetype = N'char')
select @pstrout = N'''' + REPLACE(RTRIM(CAST(@ssvar as nvarchar(max))), '''', '''''') + N''''
else if (@basetype = N'nchar')
select @pstrout = N'N''' + REPLACE(RTRIM(CAST(@ssvar as nvarchar(max))), '''', '''''') + N''''
else if (@basetype in (N'binary',N'varbinary'))
select @pstrout = sys.fn_varbintohexsubstring(1, CAST(@ssvar as varbinary(max)), 1, 0)
else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
select @pstrout = CAST(@ssvar as nvarchar(40))
else if (lower(@basetype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
select @pstrout = CONVERT(nvarchar(60), @ssvar, 2)
else if (@basetype in (N'money',N'smallmoney'))
select @pstrout = CONVERT(nvarchar(40), @ssvar, 2)
else if (@basetype = N'uniqueidentifier')
select @pstrout = N'''' + CAST(@ssvar as nvarchar(40)) + N''''
else if (@basetype in (N'datetime',N'smalldatetime'))
select @pstrout = N'''' + CONVERT(nvarchar(40), @ssvar, 112) + N' ' + CONVERT(nvarchar(40), @ssvar, 114) + N''''
else if (@basetype in (N'date',N'time',N'datetime2',N'datetimeoffset'))
select @pstrout = N'''' + CONVERT(nvarchar(40), @ssvar, 121) + N''''
else
select @pstrout = N'''Invalid Datatype' + @basetype + N'(' + CAST(@ssvar as nvarchar(max)) + N')'''
end
-- All done
return @pstrout
end

USAGE

SELECT sys.fn_sqlvarbasetostr(yourcolname) AS StringValue from YOURTABLE

Note: While searching for existing material on the subject, I came across a nice article by SQL Server MVP Peter Larsson - Convert Binary Value to String Value