create function dbo.ConvertFromBase64String (@Base64Value varchar(max)) returns varbinary(max) as begin return cast('' as xml).value('xs:base64Binary(sql:variable("@Base64Value"))', 'varbinary(max)'); end; go create function dbo.ConvertToBase64String (@BinaryValue varbinary(max)) returns varchar(max) as begin return cast('' as xml).value('xs:base64Binary(sql:variable("@BinaryValue"))','varchar(max)'); end; go declare @b varbinary(max) = cast('hello' as varbinary(max)); select @b; -- 0x68656C6C6F declare @b64 varchar(max); select @b64 = dbo.ConvertToBase64String(@b); select @b64; -- aGVsbG8= declare @result varbinary(max); select @result = dbo.ConvertFromBase64String(@b64); select @result; -- 0x68656C6C6F declare @t varchar(max); select @t = cast(@result as varchar(max)); select @t; -- hello
If you learned something today