MS SQL: URL Encode a string in Transact-SQL

Microsoft SQL ServerLeave a 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)
AS
 begin
    -- 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)
    begin
        -- Get the character
        set @strChar = substring(@strUrl, @i, 1)

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

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

Leave a Reply

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

Back To Top


Subscribe to our newsletter