SQL URL编码跟汉字相互转换

 

URL编码转为汉字：

转码前：%E5%B0%8A%E6%95%AC%E7%9A%84%E4%BC%9A%E5%91%98

转码后：尊敬的会员

SELECT dbo.FN_URLDecode('%E5%B0%8A%E6%95%AC%E7%9A%84%E4%BC%9A%E5%91%98')

create FUNCTION [dbo].[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

汉字转为URL编码：

转码前：尊敬的会员

转码后：%E5%B0%8A%E6%95%AC%E7%9A%84%E4%BC%9A%E5%91%98

SELECT dbo.FN_URLEncode('尊敬的会员')

create FUNCTION [dbo].[FN_URLEncode](
	@str nvarchar(max)
)
RETURNS nvarchar(max)
BEGIN
/*
U-00000000 ... U-0000007F 0xxxxxxx 
U-00000080 ... U-000007FF 110xxxxx 10xxxxxx 
U-00000800 ... U-0000FFFF 1110xxxx 10xxxxxx 10xxxxxx 
*/
    DECLARE @ucs2 varbinary(max)
	set @ucs2 = CAST(@str as varbinary(max))
	DECLARE @output varbinary(max), @i int, @code int
	SET @output = 0x
	SET @i = 1
	
	WHILE 1 = 1
	BEGIN
		SET @code = CAST(SUBSTRING(@ucs2,@i+1,1) + SUBSTRING(@ucs2,@i,1) AS int)
		IF @code = 0
			BREAK
		IF @code >= 0x0800
			SET @output = @output +
							CAST(@code / 4096 + 224 AS binary(1)) +
							CAST((@code % 4096) / 64 + 128 AS binary(1)) +
							CAST((@code % 4096) % 64 + 128 AS binary(1))
		ELSE IF @code >= 0x0080
			SET @output = @output +
							CAST(@code / 64 + 192 AS binary(1)) +
							CAST(@code % 64 + 128 AS binary(1))
		ELSE
			SET @output = @output + CAST(@code AS binary(1))
			SET @i = @i + 2
	END


	DECLARE @re VARCHAR(8000),@ri INT
    SELECT @re='',@ri=datalength(@output)
    WHILE @ri>0
        SELECT @re='%'+substring('0123456789ABCDEF',substring(@output,@ri,1)/16+1,1)
                +substring('0123456789ABCDEF',substring(@output,@ri,1)%16+1,1)
                +@re
            ,@ri=@ri-1
    
	RETURN(@re)

END