Tag: Microsoft SQL

Base64 encoding is a Swiss Army knife function for every programmer but somehow not readily available in Microsoft SQL Server. Or that’s what everyone thinks.

Apparently it has been hiding in MS SQL since 2005 release. So recently while searching for a working solution, I found a something workable at this blog post and then used that to create two SQL user defined functions.

Use freely but attribute.

CREATE function [dbo].[base64encode] (@input varchar(max) )

returns varchar(max)

begin

declare @source varbinary(max)

set @source = convert(varbinary(max), @input)

return cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

end

GO

CREATE function [dbo].[base64decode] (@input varchar(max) )

returns varchar(max)

begin

return cast('' as xml).value('xs:base64Binary(sql:variable("@input"))', 'varbinary(max)')

end

GO