SQL Server 2005 - Convert any data to Base64 and back

[CVMichael]

Even though the actual conversion is a one line code, it is still better to have it in a function (at least you don't have to remember all that XML stuff...)

The first function f_BinaryToBase64 takes binary data as input, but anything can be converted to binary.

The second function f_Base64ToBinary takes the Base64 data (the output of the first function), and outputs the original binary data (that can be converted back to your data type).

How to use with strings for example:

DECLARE @Base64Data VARCHAR(MAX)
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX),'testing...'))
 
SELECT @Base64Data AS Base64, CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary(@Base64Data)) AS My_Data

And the actual functions:

-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20090123>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.f_BinaryToBase64
(
    @bin VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Base64 VARCHAR(MAX)
    
    /*
        SELECT dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), 'Converting this text to Base64...'))
    */
    
    SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
    
    RETURN @Base64
END
 
 
-- =============================================
-- Author:      <Michael Ciurescu>
-- Create date: <20090123>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.f_Base64ToBinary
(
    @Base64 VARCHAR(MAX)
)
RETURNS VARBINARY(MAX)
AS
BEGIN
    DECLARE @Bin VARBINARY(MAX)
    
    /*
        SELECT CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary('Q29udmVydGluZyB0aGlzIHRleHQgdG8gQmFzZTY0Li4u'))
    */
    
    SET @Bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')
 
    RETURN @Bin
END