编码
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
解码
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