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:
sql Code:
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:
sql Code:
-- =============================================
-- 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