MS SQL: Url Encode

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 MS SQL 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