TSQL - How to URL Encode
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
Daniel Hutmacher from SQL Sunday has provided a nice function.
https://sqlsunday.com/2013/04/07/url-encoding-function/
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
CREATE FUNCTION dbo.fn_UrlEncode(@string varchar(max))
RETURNS varchar(max)
AS BEGIN
DECLARE @offset int, @char char(1);
SET @string = REPLACE(@string, '%', '%' + dbo.fn_Char2Hex('%'));
SET @offset=PATINDEX('%[^A-Z0-9.\-\%]%', @string);
WHILE (@offset!=0) BEGIN;
SET @char = SUBSTRING(@string, @offset, 1);
SET @string = REPLACE(@string, @char, '%' + dbo.fn_Char2Hex(@char));
SET @offset = PATINDEX('%[^A-Z0-9.\-\%]%', @string);
END
RETURN @string;
END;
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.
Personally I would do this in the application rather than the DB - but if you have to for some reason and you can enable CLR Integration then this would be a perfect candidate for a CLR UDF. It'd be simpler than trying to do it in SQL and probably more reliable and performant too.
There are INET_URIEncode
and INET_URIDecode
functions in the free version of the SQLsharp T-SQL CLR extension library. It handles Unicode too, though you need the paid version to handle non-standard %uXXYY
encoding.