SQL Functions for Binary to Base64 conversion

/***********************************************

-- Used to encode file attachments for CRM SQL tables

-- SQL to convert varbinary(max) data to Base64 data type

-- and vice-versa

-- Author: <Michael Ciurescu>

-- Create date: <20090123>

--Copied from

--http://www.vbforums.com/showthread.php?554886-SQL-Server-2005-Convert-any-data-to-Base64-and-back

**************************************************/

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

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