MS SQL: URL Encode a string in Transact-SQL

Microsoft SQL Server1 Comment on MS SQL: URL Encode a string in Transact-SQL

MS SQL: URL Encode a string in Transact-SQL

There is no built-in function in Microsoft SQL Server to to support URL encoding. If you want to generate URLs with arguments on the fly in stored procedures, you would have to do the URL encoding yourself. URL encoding can be implemented in several ways. Either you can create your own custom userdefined function in Microsoft SQL Server, or create a custom library in .NET. The library can then be called from a stored procedure.

Here I will show how you can encode your URLs using a userdefined function:

CREATE FUNCTION [dbo].[audf_Common_UrlEncode](@strUrl varchar(max))
returns varchar(max)
    -- Declare variables
    declare @intCount int,
            @strChar char(1),
            @i int,
            @strUrlReturn varchar(max)

    -- Initialize variables
    set @intCount       = Len(@strUrl);
    set @i              = 1;
    set @strUrlReturn   = '';  

    -- Loop through all characters
    while (@i <= @intCount)
        -- Get the character
        set @strChar = substring(@strUrl, @i, 1)

        -- Is ASCII character?
        if @strChar LIKE '[A-Za-z0-9()''*-._! ]'
            -- Just append character
            set @strUrlReturn = @strUrlReturn + @strChar
            -- Encode and append character
            set @strUrlReturn =
                   @strUrlReturn +
                   '%' +
                   SUBSTRING(sys.fn_varbintohexstr(CAST(@strChar as varbinary(max))),3,2)
        -- Next character
        set @i = @i +1

    -- Return the encoded URL
    return @strUrlReturn
Ulf Emsoy has long working experience in project management, software development and supply chain management.

One thought on “MS SQL: URL Encode a string in Transact-SQL

  1. Your mode of describing all in this article is genuinely good, all
    can easily understand it, Thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top