原文:

-- =============================================-- 作    者: ruijc-- 描    述: 将Url编码转明文字符串-- =============================================CREATE FUNCTION FN_URLDecode( @Str VARCHAR(8000)--已经编码的字符串)RETURNS VARCHAR(8000)ASBEGIN  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;ENDGOSELECT dbo.FN_URLDecode('%E4%BD%A0%E6%98%AF%E5%93%AA%E4%B8%AA')