Encode URL

by 
create function urlencode(@str as varchar(4000))
returns varchar(4000)
as
begin
declare @hex char(16)
declare @c char(1)
set @hex='0123456789ABCDEF'
declare @ostr varchar(4000)
set @ostr=''
declare @l int
set @l = 1
while @l <= len(@str)
begin
 set @c = substring(@str,@l,1)
 if @c between '0' and '9'
 or @c between 'A' and 'Z'
 or @c between 'a' and 'z'
  set @ostr = @ostr + @c
  else
  set @ostr = @ostr + '%' +
    substring(@hex,(ascii(@c)/16)+1,1)
   +substring(@hex,(ascii(@c)&15)+1,1)
 set @l=@l+1
end
return @ostr
end
go

Version 2

CREATE FUNCTION dbo.UrlEncode(@url NVARCHAR(1024)) RETURNS NVARCHAR(3072)
AS
BEGIN
DECLARE @count INT, @c NCHAR(1), @i INT, @urlReturn NVARCHAR(3072)
SET @count = LEN(@url)
SET @i = 1
SET @urlReturn = ”
WHILE (@i <= @count)
BEGIN
SET @c = SUBSTRING(@url, @i, 1)
IF @c LIKE N'[A-Za-z0-9()”*\-._!~]’ COLLATE Latin1_General_BIN ESCAPE N’\’ COLLATE Latin1_General_BIN
BEGIN
SET @urlReturn = @urlReturn + @c
END
ELSE
BEGIN
SET @urlReturn =
@urlReturn + ‘%’
+ SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),3,2)
+ ISNULL(NULLIF(SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS VARBINARY(MAX))),5,2), ’00’), ”)
END
SET @i = @i +1
END
RETURN @urlReturn
END

Also Char to Hex

CREATE FUNCTION dbo.fn_char2hex(@char char(1))
RETURNS char(2)
AS BEGIN

DECLARE @hex char(2), @dec int;
SET @dec=ASCII(@char);
SET @hex= — First hex digit:
SUBSTRING(‘0123456789ABCDEF’, 1+(@dec-@dec%16)/16, 1)+
— Second hex digit:
SUBSTRING(‘0123456789ABCDEF’, 1+( @dec%16) , 1);
RETURN(@hex);
END

Leave a Comment