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);
*
*
*