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

Author: Adedeji Olowe

Adédèjì is the founder of Lendsqr, the loan infrastructure fintech powering lenders at scale. Before this, he led Trium Limited, the corporate VC of the Coronation Group, which invested in Woven Finance, Sparkle Bank, Clane, and L1ght, amongst others. He has almost two decades of banking experience, including stints as the Divisional Head of Electronic Banking at Fidelity Bank Plc. He drove the turnaround of the bank’s digital business. He was previously responsible for United Bank for Africa Group’s payment card business across 19 countries. Alongside other industry veterans, he founded Open Banking Nigeria, the nonprofit driving the development and adoption of a common API standard for the Nigerian financial industry. Beyond open APIs, Adédèjì works deeply within the fintech ecosystem; he’s the board chairman at Paystack. Adédèjì is a renowned fintech pundit and has been blogging on technology and payments at dejiolowe.com since 2001.

One thought on “Base64 in Microsoft SQL”

Leave a Reply

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