/***********************************************
-- 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