URL Encoding is the transformation of special characters into a form which can be passed over the internet. A good example being ” ” (space) which should be replaced by “%20%”.
The following SQL function can be used to URL Encode a string in SQL:
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
In order to use the function, call it as follows:
SELECT dbo.UrlEncode('This is a test, *&%"£$')
This gives the following result:
Thanks to Peter DeBetta for the SQL.
This is a nice trick, thanks for sharing Jonathan.
Do you know if it will handle and embedded “&” character? This can be a problem. Also, sometimes we get caught reading a string that has originated from and email typed in Outlook, which contains what MS describes as smart-quotes or stretch hyphens.
With regards the &, the script changes this to %26, which should be supported when passed via a web service call.
The “smart quotes” isn’t supported, however if you add the following to the top of the query, (after BEGIN, before DECLARE), this will convert the characters first:
SET @url = Replace(@url,CHAR(147),'”‘)
SET @url = Replace(@url,CHAR(148),'”‘)
SET @url = Replace(@url,CHAR(145),””)
SET @url = Replace(@url,CHAR(146),””)
SET @url = Replace(@url,CHAR(150),’-‘)
SET @url = Replace(@url,CHAR(151),’–‘)
SET @url = Replace(@url,CHAR(133),’…’)
Tested working with the quotation marks from Word, not tested with other characters, but in theory should work with those 🙂
Thanks but it doesn’t work with non-ascii characters. Any Tips?
Unfortunately non-ascii can get very messy! More details here. I cannot think of a way around this which would work reliabily.