GO

CREATE FUNCTION [dbo].[c_GetUTF8Code]

   (

       @char Nchar

   )

RETURNS int

AS

--UTF8转码

BEGIN

 Declare @Code int

 Select @Code=Cast(Unicode(@char) as int)

 Declare @Utf8Code int

 Set @Utf8Code=0

 if(@Code<128)

 begin

  --0-127

  --0000-007F

  --0xxxxxxx

  --01100010 Unocide

  --01100010 UTF-8

  Set @Utf8Code=@Code

 end

 else if(@Code>127 and @Code<2048)

 begin

  --128-2047

  --0080-07FF

  --110xxx xx10xx xxxx

  --110  7      F    F

  Declare @C1 int

  Declare @C2 int

  Declare @C3 int

  Select @C1=@Code/0x100

  Select @C2=(@Code%0x100)/0x10

  Select @C3=@Code%0x10

  Select @Utf8Code=0xC080+0x400*@C1+0x100*(@C2/4)+0x10*(@C2%4)+@C3

 end

 else if(@Code>2047 and @Code<65536)

 begin

  --2047-65535

  --0110 0010 0001 0001

  --1110 xxxx 10xx xxxx 10xx xxxx

  --1110 0110 1000 1000 1001 0001

  Declare @C11 int

  Declare @C12 int

  Declare @C13 int

  Declare @C14 int

  Select @C11=@Code/0x1000

  Select @C12=(@Code%0x1000)/0x100

  Select @C13=(@Code%0x100)/0x10

  Select @C14=@Code%0x10

  Select @Utf8Code=0xE08080+0x10000*@C11+0x400*@C12+0x100*(@C13/4)+0x10*(@C13%4)+@C14

 end

 return @Utf8Code

End

GO

CREATE FUNCTION [dbo].[base64_utf8encode]

(

 @plain_text varchar(max)

)

RETURNS varchar(max)

AS BEGIN

--Base64解密

 DECLARE @output varchar(max)

 DECLARE @block_start integer

 DECLARE @map char(64)

 SET @map='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'

 SET @output=''

 SET @block_start=0

 Declare @plain_textLength int

 Set @plain_textLength=Len(@plain_text)

 Declare @RestTransfer int--转码数累积

 Declare @RestTransferLenth int

 Set @RestTransfer=0

 Set @RestTransferLenth=0

 Declare @CodeInt int

 Declare @block_val BINARY(3)

 WHILE @block_start<@plain_textLength

 BEGIN

  Set @CodeInt=0

  SELECT @CodeInt= [dbo].[c_GetUTF8Code](SubString(@plain_text,@block_start+1,1))

  Declare @CodeTransfer int

  Set @CodeTransfer=0

  --0-127 1位

  --128-2047 2位

  --2047-65535 3位

  if(@CodeInt<128)

  begin

   --+1位

   if(@RestTransferLenth=0 or @RestTransferLenth=1)

   begin

    Set @RestTransfer=@RestTransfer*0x100+@CodeInt

    Set @RestTransferLenth=@RestTransferLenth+1

   end

   else if(@RestTransferLenth=2)

   begin

    Set @CodeTransfer=@RestTransfer*0x100+@CodeInt

    Set @RestTransfer=0

    Set @RestTransferLenth=0

   end

  end

  else if(@CodeInt>127 and @CodeInt<2048)

  begin

   --+2位

   if(@RestTransferLenth=0)

   begin

    Set @RestTransfer=@CodeInt

    Set @RestTransferLenth=2

   end

   else if(@RestTransferLenth=1)

   begin

    Set @CodeTransfer=0x10000*@RestTransfer+@CodeInt

    Set @RestTransfer=0

    Set @RestTransferLenth=0

   end

   else if(@RestTransferLenth=2)

   begin

    Set @CodeTransfer=0x100*@RestTransfer+@CodeInt/0x100

    Set @RestTransfer=@CodeInt%0x100

    Set @RestTransferLenth=1

   end

  end

  else if(@CodeInt>2047)

  begin

   --+3位

   if(@RestTransferLenth=0)

   begin

    Set @CodeTransfer=@CodeInt

    Set @RestTransfer=0

    Set @RestTransferLenth=0

   end

   else if(@RestTransferLenth=1)

   begin

    Set @CodeTransfer=0x10000*@RestTransfer+@CodeInt/0x100

    Set @RestTransfer=@CodeInt%0x100

    Set @RestTransferLenth=1

   end

   else if(@RestTransferLenth=2)

   begin

    --剩余部分十六进制右移两位与新数据前两位之和

    Set @CodeTransfer=0x100*@RestTransfer+@CodeInt/0x10000

    Set @RestTransfer=@CodeInt%0x10000

    Set @RestTransferLenth=2

   end

  end

  ---累积到3位，执行加密转换

  if(@CodeTransfer>0x100000)

  begin

   SET @block_val = CAST(@CodeTransfer AS BINARY(3))

   SET @output = @output

   + SUBSTRING(@map , @block_val/262144  +1,1)

   + SUBSTRING(@map ,(@block_val/4096&63)+1,1)

   + SUBSTRING(@map ,(@block_val/64  &63)+1,1)

   + SUBSTRING(@map ,(@block_val&63)     +1,1)

  end

  SET @block_start=@block_start+1

 END

 IF @RestTransferLenth>0

  BEGIN 

  SET @block_val=Cast(@RestTransfer*(Case @RestTransferLenth When 1 Then 65536 Else 256 end) as BINARY(3))

  SET @output=@output

   +SUBSTRING(@map , @block_val/262144+1,    1)

   +SUBSTRING(@map ,(@block_val/4096  &63)+1,1)

   +CASE WHEN @RestTransferLenth =1

   THEN REPLACE(SUBSTRING(@map ,(@block_val/64&63)+1,1),'A','=')

   ELSE SUBSTRING(@map ,(@block_val/64&63)+1,1)

    END

   +CASE WHEN @RestTransferLenth=1

   THEN '='

   ELSE REPLACE(SUBSTRING(@map ,(@block_val&63)+1,1),'A','=')

    END

  END

 RETURN @output

END

GO

CREATE FUNCTION [dbo].[base64_utf8decode]

   (

       @encoded_text varchar(max)

   )

   RETURNS varchar(max)

   AS BEGIN

--BASE64加密

DECLARE @output varchar(max)

DECLARE @block_start int

DECLARE @encoded_length int

DECLARE @decoded_length int

DECLARE @mapr binary(122)

SET @output = ''

SET @mapr =

  0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF    --    1-33

  +0xFFFFFFFFFFFFFFFFFFFF3EFFFFFF3F3435363738393A3B3C3DFFFFFF00FFFFFF    --    33-64

  +0x000102030405060708090A0B0C0D0E0F10111213141516171819FFFFFFFFFFFF    --    65-96

  +0x1A1B1C1D1E1F202122232425262728292A2B2C2D2E2F30313233--    97-122

SET @encoded_length=LEN(@encoded_text)

SET @decoded_length=@encoded_length/4*3

SET @block_start=1

Declare @Code int

Set @Code=0

Declare @CodeLength int--累计连接数，1，2，3

Set @CodeLength =0

WHILE @block_start<@encoded_length

BEGIN

 Declare @Integer Integer

 Set @Integer=substring(@mapr,Unicode(substring(@encoded_text,@block_start  ,1)),1)*262144

     + substring(@mapr,Unicode(substring(@encoded_text,@block_start+1,1)),1)*4096

     + substring(@mapr,Unicode(substring(@encoded_text,@block_start+2,1)),1)*64

     + substring(@mapr,Unicode(substring(@encoded_text,@block_start+3,1)),1)

 Declare @C1 int

 Declare @C2 int

 Declare @C3 int

 --0xFF FF FF

 Set @C1=@Integer/0x10000

 Set @C2=(@Integer/0x100)%0x100

 Set @C3=@Integer%0x100

 -------------------------------------@C1

 if(@C1<0x80)

 begin

  if(@CodeLength=2)

  begin

   --128-2047

   --0080-07FF

   --110x xx xx 10xx xxxx

   Set @Code=((@Code%0x2000)/0x100)*0x10+@Code%0x40

   SET @output=@output+NCHAR(@Code)

   --print @Code

   Set @Code=0

   Set @CodeLength=0

  end

  SET @output=@output+CAST(Cast(@C1 AS BINARY(1))AS VARCHAR(1))

 end

 else

 begin

  --码字连接

  Set @Code=@Code*0x100+@C1

  SET @CodeLength=@CodeLength+1

  if(@CodeLength=3)

  begin

   --0110 0010 0001 0001

   --1110 xxxx 10xx xxxx 10xx xxxx

   --1110 0110 1000 1000 1001 0001

   Set @Code=((@Code%0x100000)/0x10000)*0x1000+((@Code%0x4000)/0x100)*0x40+@Code%0x40

   SET @output=@output+NCHAR(@Code)

   Set @Code=0

   Set @CodeLength=0

  end

 end

 -------------------------------------@C2

 if(@C2<0x80)

 begin

  if(@CodeLength=2)

  begin

   --128-2047

   --0080-07FF

   --110x xx xx 10xx xxxx

   Set @Code=((@Code%0x2000)/0x100)*0x10+@Code%0x40

   SET @output=@output+NCHAR(@Code)

   --print @Code

   Set @Code=0

   Set @CodeLength=0

  end

  SET @output=@output+CAST(Cast(@C2 AS BINARY(1))AS VARCHAR(1))

 end

 else

 begin

  --码字连接

  Set @Code=@Code*0x100+@C2

  SET @CodeLength=@CodeLength+1

  if(@CodeLength=3)

  begin

   --0110 0010 0001 0001

   --1110 xxxx 10xx xxxx 10xx xxxx

   --1110 0110 1000 1000 1001 0001

   Set @Code=((@Code%0x100000)/0x10000)*0x1000+((@Code%0x4000)/0x100)*0x40+@Code%0x40

   SET @output=@output+NCHAR(@Code)

   Set @Code=0

   Set @CodeLength=0

  end

 end

 -------------------------------------@C3

 if(@C3<0x80)

 begin

  if(@CodeLength=2)

  begin

   --128-2047

   --0080-07FF

   --110x xx xx 10xx xxxx

   Set @Code=((@Code%0x2000)/0x100)*0x10+@Code%0x40

   SET @output=@output+NCHAR(@Code)

   --print @Code

   Set @Code=0

   Set @CodeLength=0

  end

  SET @output=@output+CAST(Cast(@C3 AS BINARY(1))AS VARCHAR(1))

 end

 else

 begin

  --码字连接

  Set @Code=@Code*0x100+@C3

  SET @CodeLength=@CodeLength+1

  if(@CodeLength=3)

  begin

   --0110 0010 0001 0001

   --1110 xxxx 10xx xxxx 10xx xxxx

   --1110 0110 1000 1000 1001 0001

   Set @Code=((@Code%0x100000)/0x10000)*0x1000+((@Code%0x4000)/0x100)*0x40+@Code%0x40

   SET @output=@output+NCHAR(@Code)

   Set @Code=0

   Set @CodeLength=0

  end

 end

 SET @block_start = @block_start + 4

END

IF RIGHT(@encoded_text,2)='=='

 SET @decoded_length=@decoded_length-2

ELSE IF RIGHT(@encoded_text,1)='='

 SET @decoded_length=@decoded_length-1

RETURN LEFT(@output ,@decoded_length)

END