Saturday, October 8, 2011

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
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

0 kommentarer:

Post a Comment