Base64Encode, Base64Decode 스칼라 함수

(1) Base64Encode

DROP FUNCTION IF EXISTS [dbo].[Base64Encode] GO   CREATE FUNCTION [dbo].[Base64Encode]  (      @v_Plain VARCHAR(MAX)  ) RETURNS VARCHAR(MAX) BEGIN     DECLARE @v_Encoded VARCHAR(MAX)       SELECT @v_Encoded = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'VARCHAR(MAX)')     FROM      (         SELECT CAST(@v_Plain AS VARBINARY(MAX)) AS bin     ) AS bin_sql_server_temp;       RETURN @v_Encoded END GO

 

(2) Base64Decode

DROP FUNCTION IF EXISTS [dbo].[Base64Decode] GO CREATE FUNCTION [dbo].[Base64Decode]  (      @v_Encoded VARCHAR(MAX)  ) RETURNS VARCHAR(MAX) BEGIN     DECLARE @Decoded VARCHAR(MAX)      SELECT @Decoded = CAST(CAST(N'' AS XML).value('xs:base64Binary(sql:column("bin"))', 'VARBINARY(MAX)') AS VARCHAR(MAX))     FROM      (         SELECT CAST(@v_Encoded AS VARCHAR(MAX)) AS bin     ) AS bin_sql_server_temp;       RETURN @Decoded END GO

 

(3) 테스트

DECLARE @v_PlainText VARCHAR(MAX) DECLARE @v_Encoded VARCHAR(MAX) SET @v_PlainText = 'codefactoryglobal' SELECT @v_Encoded = dbo.Base64Encode(@v_PlainText) SELECT @v_Encoded AS Encoded, dbo.Base64Decode(@v_Encoded) AS PlainText