TSQL - How to URL Encode

Question

Looking for a bug free tested sql script that i could use in a UDF to encode a url through sql. Function would take in a URL and pass out a URL Encoded URL. I have seen a few, but all i have come across seem to have some flaws.

Answer 1

How about this one by Peter DeBetta:

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

Answer 2

The question specifically asks for a function however here is a solution to url encode if your not able to create any functions:

select replace27
from TableName
cross apply (select replace1 = replace(T.TagText, '%', '%25')) r1
cross apply (select replace2 = replace(replace1, '&', '%26')) r2
cross apply (select replace3 = replace(replace2, '$', '%24')) r3
cross apply (select replace4 = replace(replace3, '+', '%2B')) r4
cross apply (select replace5 = replace(replace4, ',', '%2C')) r5
cross apply (select replace6 = replace(replace5, ':', '%3A')) r6
cross apply (select replace7 = replace(replace6, ';', '%3B')) r7
cross apply (select replace8 = replace(replace7, '=', '%3D')) r8
cross apply (select replace9 = replace(replace8, '?', '%3F')) r9
cross apply (select replace10 = replace(replace9, '@', '%40')) r10
cross apply (select replace11 = replace(replace10, '#', '%23')) r11
cross apply (select replace12 = replace(replace11, '<', '%3C')) r12
cross apply (select replace13 = replace(replace12, '>', '%3E')) r13
cross apply (select replace14 = replace(replace13, '[', '%5B')) r14
cross apply (select replace15 = replace(replace14, ']', '%5D')) r15
cross apply (select replace16 = replace(replace15, '{', '%7B')) r16
cross apply (select replace17 = replace(replace16, '}', '%7D')) r17
cross apply (select replace18 = replace(replace17, '|', '%7C')) r18
cross apply (select replace19 = replace(replace18, '^', '%5E')) r19
cross apply (select replace20 = replace(replace19, ' ', '%20')) r20
cross apply (select replace21 = replace(replace20, '~', '%7E')) r21
cross apply (select replace22 = replace(replace21, '`', '%60')) r22
cross apply (select replace23 = replace(replace22, '*', '%2A')) r23
cross apply (select replace24 = replace(replace23, '(', '%28')) r24
cross apply (select replace25 = replace(replace24, ')', '%29')) r25
cross apply (select replace26 = replace(replace25, '/', '%2F')) r26
cross apply (select replace27 = replace(replace26, '\', '%5C')) r27

The limitation of this solution it that is does not replace ASCII Control characters or Non-ASCII characters.

Note it's important that the first replacement is for % so we don't escape any escape codes.