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:
This%20is%20a%20test%2c%20*%26%25%22%a3%24
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.