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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook