Base64 in 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