SQL Server has built-in support for base64 encoding and decoding

Did you know that SQL Server has built-in support for base64 encoding and decoding and has since 2005? I sure didn’t, because it’s hidden in the depths of XQuery. Here is a complete sample showing a complete round trip from string to binary to base64, back to binary and back to string again.

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)

set @source = convert(varbinary(max), 'Hello Base64')
set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar