-- =============================================
-- Author: Jeffrey Lee
-- Create date: 2007-08-03
-- Description: Provide UrlEncode function in SQL Server
-- =============================================
CREATE FUNCTION 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