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


Discover more from Adedeji Olowe

Subscribe to get the latest posts sent to your email.

Author: Adedeji Olowe

Adedeji / a bunch of bananas ate a monkey /

One thought on “Base64 in Microsoft SQL”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Adedeji Olowe

Subscribe now to keep reading and get access to the full archive.

Continue reading