SQL Server Base64函数

sunren

IF OBJECT_ID(N'dbo.c_GetUTF8Code') IS NOT NULL
DROP FUNCTION dbo.c_GetUTF8Code;
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
-------加密函数[dbo].[base64_utf8encode]----
IF OBJECT_ID(N'dbo.base64_utf8encode') IS NOT NULL
DROP FUNCTION dbo.base64_utf8encode;
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
----------解密函数----------
IF OBJECT_ID(N'dbo.base64_utf8decode') IS NOT NULL
DROP FUNCTION dbo.base64_utf8decode;
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