SqlServer 编码解码

1、解码方法定义

CREATE FUNCTION FN_URLDecode  
(  
 @Str VARCHAR(8000)--已经编码的字符串  
)  
RETURNS VARCHAR(8000)  
AS  
BEGIN  
  DECLARE @Position  INT;          --'%'字符所在位置  
  DECLARE @Chr       CHAR(16);     --字符常量  
  DECLARE @Pattern   CHAR(21);  
  DECLARE @ParseStr  VARCHAR(8000);--解码后的字符串  
  DECLARE @Hex       UNIQUEIDENTIFIER;--定义16进制模板,因为GUID方便转为BYTE  
  DECLARE @CurrWord  INT        ;--当前字  
  DECLARE @BitsCount INT        ;--当前解码位数  
  DECLARE @HightByte TINYINT;--高位字节  
  DECLARE @LowByte   TINYINT;--低位字节  
    
  /****************变量初始化***********************/  
  SET     @Chr = '0123456789abcdef';  
  SET     @Pattern = '%[%][a-f0-9][a-f0-9]%';  
  SET     @ParseStr=@Str;  
  SET     @Hex= '00000000-0000-0000-0000-000000000000';  
  SET     @CurrWord=0;  
  SET     @BitsCount=0;  
  SET     @HightByte=0;  
  SET     @LowByte=0;  
    
  IF (@Str IS NOT NULL OR @Str<>'')  
   BEGIN  
     SET    @Position = PATINDEX(@Pattern, @ParseStr);--取得第一个'%'所在的位置  
     WHILE @Position>0  
      BEGIN  
        SET @Hex=STUFF(@Hex,7,2,LEFT(RIGHT(@ParseStr,len(@ParseStr) - @Position),2));  
        SET @HightByte=CAST(CAST(@Hex AS BINARY(1)) AS INT);  
          
        IF (@HightByte & 127=@HightByte)  
         BEGIN--ASCII码直接转为UTF-8或UTF-16  
           SET @CurrWord=@HightByte;  
           SET @BitsCount=1;  
         END  
           
        IF (@HightByte & 192=192)  
         BEGIN--Unicode编码  
           SET @CurrWord=@HightByte & 31 ;  
           SET @BitsCount=2;  
         END  
  
        IF (@HightByte & 224=224)  
         BEGIN--UTF-8编码  
            SET @CurrWord = @HightByte & 15  
            SET @BitsCount = 3    
         END  
  
        IF (@HightByte & 240=240)  
         BEGIN--UTF-16编码  
            SET @CurrWord = @HightByte & 7  
            SET @BitsCount = 4    
         END  
  
        DECLARE @Index INT;          
        DECLARE @NEWCHAR NVARCHAR(2);  
        SET @Index=1;  
        SET @NEWCHAR='';  
        WHILE @Index<@BitsCount  
         BEGIN  
              IF (LEN(@ParseStr)-@Position-3*@Index)<0  
               BEGIN  
                   SET @ParseStr=@Str ;     
                   SET @Position=0;  
                   BREAK;                
               END  
            SET @NEWCHAR = LEFT(RIGHT(@ParseStr,LEN(@ParseStr) - @Position - 3* @Index),2);     
            --如果没有16进制编码则中断外层WHILE  
            IF @NEWCHAR NOT LIKE '[a-f0-9][a-f0-9]'  
             BEGIN  
                SET @ParseStr = @Str  
                SET @Position=0;--中断外层WHILE  
                BREAK;  
             END      
  
            SET @Hex = STUFF(@Hex, 7, 2, @NEWCHAR)        
  
            SET @LowByte = CAST(CAST(@Hex AS BINARY(1)) AS INT);  
  
            IF @LowByte&192=192  
            BEGIN  
                SET @ParseStr = @Str  
                SET @Position=0;--中断外层WHILE  
                BREAK;  
            END   
              
            SET @CurrWord = (@CurrWord * 64) | (@LowByte & 63)                
            SET @Index =@Index+ 1                                                  
         END                                     
  
         IF @BitsCount > 1             
          SET @ParseStr = STUFF(@ParseStr, @Position, 3*(@BitsCount), NCHAR(@CurrWord))  
         ELSE   
          BEGIN  
            set @ParseStr = STUFF(@ParseStr, @Position, 2, NCHAR(@CurrWord))  
            set @ParseStr = STUFF(@ParseStr, @Position+1, 1, N'')         
          END  
        ----取得下一个'%'所在的位置  
        SET  @Position = PATINDEX(@Pattern, @ParseStr);  
      END  
   END  
   RETURN @ParseStr;  
END  

2、解码方法调用

select dbo.FN_URLDecode('%E5%B9%BF%E4%B8%9C%E7%9C%81%E7%8F%A0%E6%B5%B7%E5%B8%82%E4%BF%A1%E6%81%AF%E5%B7%A5%E7%A8%8B%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8')

3、编码方法定义

CREATE FUNCTION [dbo].[UrlEncode]
(
    @Param NVARCHAR(2000)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @HexStr VARCHAR(MAX)
    --Use system function to convert input string to hex string
    SET @HexStr = master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(MAX), @Param))
    --Remove the starting '0x'
    SET @HexStr = RIGHT(@HexStr, LEN(@HexStr)-2)
    --Declare required variables
    DECLARE @I INT, @Len INT
    DECLARE @Output VARCHAR(MAX), @S CHAR(4), @C CHAR(1)
    DECLARE @LoByte TINYINT, @HiByte TINYINT
    --Get length
    SET @Len=LEN(@HexStr)/4
    --Start with first character
    SET @I=0
    --Prepare the output string
    SET @Output=''
    WHILE @I<@Len
        BEGIN
        SET @S=SUBSTRING(@HexStr, @I*4 + 1, 4)
        IF RIGHT(@S, 2)='00'
            BEGIN
            --Try to convert 2 hex digits to char
            SET @LoByte = ASCII(SUBSTRING(@S, 2, 1)) - 48
            IF @LoByte>10 SET @LoByte = @LoByte - 39 --0x61'a'-> 10
            SET @HiByte = ASCII(SUBSTRING(@S, 1, 1)) - 48
            IF @HiByte>10 SET @HiByte = @HiByte - 39
            SET @C=CHAR(@LoByte + @HiByte * 16)
            --If it's a reserved character, don't encode
            IF @C LIKE '[A-Za-z0-9()''*-._! ]'
                SET @Output = @Output + @C 
            ELSE
                SET @Output = @Output + '%' + LEFT(@S, 2)
            END
        ELSE
            SET @Output = @Output + '%u' + RIGHT(@S, 2) + LEFT(@S, 2)
        --Move to next hex 
        SET @I = @I + 1
        END
    
RETURN @Output
END

4、编码方法调用

select dbo.UrlEncode('你好')

5、c#解码

System.Web.HttpUtility.UrlDecode(request.name);

*
*
*