SQLSERVER 实现UTF-8编码的URLCODE

目前在sqlserver 数据库中大部分实现的是unicode编码的URLCODE，中文都是两个字节，下面的实现汉字三个字节的转换：

1） URL编码

-- select [dbo].[UrlCode] ( N'你好' ) 
--  select [dbo].[UrlCode]( N'{"result":"false","msg":"工序不匹配","data":[]}' ) 
ALTER FUNCTION [dbo].[UrlCode](@url varchar(max))
RETURNS varchar(max)
AS
BEGIN 
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(max)
	,@s varchar(50), @code1 bigint ,@code2 bigint,@h bigint,@m bigint,@l bigint;
    SET @count = Len(@url) 
    SET @i = 1 
    SET @urlReturn = '' 
    WHILE (@i <= @count) 
    BEGIN 
        SET @s = substring(@url, @i, 1) 
		if @s like '[0-9]' or @s like '[a-z]' or @s like '[A-Z]'
		begin
		   SET @s = @s 
		end 
		else if UniCode(@s)<128 
		begin 
			select  @code1=UniCode(@s) 
			select  @s=[dbo].[varbin2hexstr](@code1)
			set  @s='%'+right(@s,2)
		end else begin 
			select  @code1=UniCode(@s),@l=UniCode(@s) &0x3F ,@m=UniCode(@s) &0xFC0 ,@h=UniCode(@s) &0xF000 
			select  @code2=@l+ 0x80  + @m*4 + 0x8000 + @h*16 + 0xE00000
			select  @s=[dbo].[varbin2hexstr](@code2)
			set  @s='%'+left(right(@s,6),2)+'%'+left(right(@s,4),2)+'%'+right(@s,2)		
		end	  
        select  @urlReturn=@urlReturn+@s
        SET @i = @i +1 
     END

    RETURN @urlReturn
END

2） URL解码

-- select [dbo].[UrlDecode] ( N'%7Ba%E6%8A%A5r%E6%97%A5' ) 
--  select [dbo].[UrlDecode]( N'%7B%22result%22%3A%22false%22%2C%22msg%22%3A%22%E5%B7%A5%E5%BA%8F%E4%B8%8D%E5%8C%B9%E9%85%8D%22%2C%22data%22%3A%5B%5D%7D' ) 
ALTER FUNCTION [dbo].[UrlDecode](@url varchar(max))
RETURNS varchar(max)
AS
BEGIN 
    DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(max)
	,@s varchar(50), @code1 bigint ,@code2 bigint,@h bigint,@m bigint,@l bigint;
    SET @count = Len(@url) 
    SET @i = 1 
    SET @urlReturn = '' 
    WHILE (@i <= @count) 
    BEGIN 
        SET @c = substring(@url, @i, 1) 
		if @c='%'
		begin 		    
			if (substring(@url, @i+1, 1) LIKE '[0-9]') and (@i+2<=@count) 
			begin 
			    set @s = substring(@url, @i, 3)
				select  @code1=CONVERT(bigint, CONVERT(varbinary,CAST('0x'+replace(@s,'%','') AS char ) , 1))
				select @urlReturn=@urlReturn+nchar(@code1) 
				SET @i = @i + 3 
			end else  if  (@i+8<=@count) 
			begin 
				set @s = substring(@url, @i, 9)
				select  @code1=CONVERT(bigint, CONVERT(varbinary,CAST('0x'+replace(@s,'%','') AS char ) , 1))
				select @h=@code1&0xF0000,@m=@code1&0x3F00,@l=@code1&0x3F 
				select @code2=(@h/16) + (@m/4) + @l
				select @urlReturn=@urlReturn+nchar(@code2)
				SET @i = @i + 9 
			end else 
			  break
			
		end else begin 
           SET @urlReturn=@urlReturn+@c 
           SET @i = @i +1 
		end
     END

    RETURN @urlReturn
END

3）上面两个函数使用到了一个转十六进制字符的函数；（来源于网络）

CREATE function [dbo].[varbin2hexstr](
    @bin varbinary(8000)
    )returns varchar(8000)
    as
    begin
        declare @re varchar(8000),@i int
        select @re='',@i=datalength(@bin)
        while @i>0
            select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
                    +substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
                    +@re
                ,@i=@i-1
       -- return('0x'+@re)
        return @re
    end
GO