URL Encode String in SQL

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.

4 thoughts on “URL Encode String in SQL

  1. Paul

    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.

  2. JRevell Post author

    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 🙂

  3. JRevell Post author

    Unfortunately non-ascii can get very messy! More details here. I cannot think of a way around this which would work reliabily.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.