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

Did you like this post?



	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal