Base64 encoding in SQL Server 2005 T-SQL

Question

I'd like to write a T-SQL query where I encode a string as a Base64 string. Surprisingly, I can't find any native T-SQL functions for doing Base64 encoding. Does a native function exist? If not, what's the best way to do Base64 encoding in T-SQL?

Answer 1

DECLARE @source varbinary(max),  
@encoded_base64 varchar(max),  
@decoded varbinary(max) 
SET @source = CONVERT(varbinary(max), 'welcome') 
-- Convert from varbinary to base64 string 
SET @encoded_base64 = CAST(N'' AS xml).value('xs:base64Binary(sql:variable       
("@source"))', 'varchar(max)') 
  -- Convert back from base64 to varbinary 
   SET @decoded = CAST(N'' AS xml).value('xs:base64Binary(sql:variable             
  ("@encoded_base64"))', 'varbinary(max)') 

 SELECT
  CONVERT(varchar(max), @source) AS [Source varchar], 
   @source AS [Source varbinary], 
     @encoded_base64 AS [Encoded base64], 
     @decoded AS [Decoded varbinary], 
     CONVERT(varchar(max), @decoded) AS [Decoded varchar]

This is usefull for encode and decode.

By Bharat J

Answer 2

I loved @Slai's answer. I only had to make very minor modifications into the one-liners I was looking for. I thought I'd share what I ended up with in case it helps anyone else stumbling onto this page like I did:

DECLARE @Source VARCHAR(50) = '12345'
DECLARE @Encoded VARCHAR(500) = CONVERT(VARCHAR(500), (SELECT CONVERT(VARBINARY, @Source) FOR XML PATH(''), BINARY BASE64))
DECLARE @Decoded VARCHAR(500) = CONVERT(VARCHAR(500), CONVERT(XML, @Encoded).value('.','varbinary(max)'))
SELECT @Source AS [Source], @Encoded AS [Encoded], @Decoded AS [Decoded]