SQL 将URL编码转汉字

来源：互联网发布：天府软件园知乎编辑：程序博客网时间：2024/06/06 12:35

转码前：%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

转码后：广东省珠海市信息工程有限公司

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')

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

转自：http://blog.csdn.net/ruijc/article/details/6931189

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')

阅读全文

0 0