[MSSQL] BASE64 함수

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

BASE 64 ENCODE 쿼리

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

CREATE FUNCTION dbo.fn_BASE64_ENC

(

 @InputStrings varchar(8000)

)

RETURNS varchar(8000)

AS

BEGIN

 DECLARE  @ConvertTable varchar(128)

  ,@ReturnStrings varchar(8000)

  ,@InputBinary varbinary(8000)

  ,@InputSize int

  ,@Count  int

  ,@Before1 binary(1)

  ,@Before2 binary(1)

  ,@Before3 binary(1)

  ,@After1 int

  ,@After2 int

  ,@After3 int

  ,@After4 int

 

 

 

  SET @ConvertTable = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    + 'abcdefghijklmnopqrstuvwxyz'

    + '0123456789+/='

  SET @InputBinary = CAST(@InputStrings AS varbinary(8000))

  SET @InputSize = DATALENGTH(@InputBinary)

  SET @Count = 1

  SET @ReturnStrings = ''

 

IF @InputStrings <>'' or @InputStrings is not null

BEGIN

  WHILE (0=0) BEGIN

   IF @Count > @InputSize BREAK

 

   SET @Before1 = SUBSTRING(@InputBinary, @Count, 1)

 

   IF @Count + 1 > @InputSize BEGIN

    SET @Before2 = NULL

   END

   ELSE BEGIN

    SET @Before2 = SUBSTRING(@InputBinary, @Count + 1, 1)

   END

 

   IF @Count + 2 > @InputSize BEGIN

    SET @Before3 = NULL

   END

   ELSE BEGIN

    SET @Before3 = SUBSTRING(@InputBinary, @Count + 2, 1)

   END

 

   SET @After1 = (@Before1 & 252) /  4

 

   SET @After2 = (@Before1 &   3) * 16

    + (ISNULL(@Before2, 0x00) & 240) / 16

 

   SET @After3 = (@Before2 &  15) *  4

    + (ISNULL(@Before3, 0x00) & 192) / 64

   SET @After4 = (ISNULL(@Before3, 0x00) &  63) *  1

 

   SET @ReturnStrings = @ReturnStrings

    + SUBSTRING(@ConvertTable, @After1 + 1, 1)

    + SUBSTRING(@ConvertTable, @After2 + 1, 1)

    + CASE WHEN @Before2 IS NULL AND @Before3 IS NULL

     THEN '='

     ELSE SUBSTRING(@ConvertTable, @After3 + 1, 1)

      END

    + CASE WHEN @Before3 IS NULL

     THEN '='

     ELSE SUBSTRING(@ConvertTable, @After4 + 1, 1)

      END

 

   SET @Count = @Count + 3

  END

 

END

 RETURN @ReturnStrings

END

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

BASE 64 ENCODE 쿼리

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

CREATE FUNCTION [dbo].[fn_BASE64_DEC]

(

 @MimeText VARCHAR(8000)

)

RETURNS VARCHAR(6000)

AS

BEGIN

-- © 2006 Peter Larsson, Developer Workshop, all rights reserved

 DECLARE @Characters VARCHAR(64), @Index SMALLINT,

  @m1 TINYINT, @m2 TINYINT, @m3 SMALLINT, @m4 SMALLINT,

  @p1 TINYINT, @p2 TINYINT, @p3 TINYINT,

  @PlainText VARCHAR(6000), @Paddings TINYINT

 SELECT @Characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',

  @MimeText = REPLACE(REPLACE(@MimeText, CHAR(13), ''), CHAR(10), ''),

  @Index = DATALENGTH(@MimeText) - 3,

  @Paddings = DATALENGTH(@MimeText) - DATALENGTH(REPLACE(@MimeText, '=', '')),

  @PlainText = ''

 WHILE @Index > 0

  SELECT @m1 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,

   @m2 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 1, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,

   @m3 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 2, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,

   @m4 = CHARINDEX(CAST(SUBSTRING(@MimeText, @Index + 3, 1) AS BINARY(1)), CAST(@Characters AS BINARY(64))) - 1,

   @p1 = (@m1 & 63) * 4 + (@m2 & 48) / 16,

   @p2 = (@m2 & 15) * 16 + (@m3 & 60) / 4,

   @p3 = (@m3 & 3) * 64 + (@m4 & 63),

   @PlainText = CHAR(@p1) + CHAR(@p2) + CHAR(@p3) + @PlainText,

   @Index = @Index - 4

 RETURN LEFT(@PlainText, DATALENGTH(@PlainText) - @Paddings)

END