(SQL Server) CanonicalizeUrl

The CanonicalizeUrl method is a utility function that canonicalizes a URL into a standard form to avoid duplicates. For example, "http://www.chilkatsoft.com/" and "http://www.chilkatsoft.com/default.asp" are the same URL.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @spider int
    -- Use "Chilkat_9_5_0.Spider" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Spider', @spider OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Does a DNS lookup to find the default domain, which may or may not include the "www." depending on the DNS results.
    -- Also domain names are converted to lowercase:
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.ChilkatSoft.com/'
    PRINT @sTmp0

    -- CanonicalizeUrl will drop the HTML fragment:
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com/purchase2.asp#buyZip'
    PRINT @sTmp0

    -- If a username/password is in the URL, it gets dropped:
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://username:password@www.chilkatsoft.com/purchase2.asp#buyZip'
    PRINT @sTmp0

    -- Port 80 and 443 are dropped:
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com:80/purchase2.asp'
    PRINT @sTmp0
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'https://www.paypal.com:443/'
    PRINT @sTmp0

    -- Removes default pages:
    -- default.asp, index.html, index.htm, default.html, index.htm, default.htm
    -- index.php, index.asp, default.php, .cfm, .aspx, ,php3, .pl, .cgi, .txt, .shtml, .phtml
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com/index.asp'
    PRINT @sTmp0
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com/index.asp'
    PRINT @sTmp0
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com/index.php'
    PRINT @sTmp0
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com/index.pl'
    PRINT @sTmp0
    EXEC sp_OAMethod @spider, 'CanonicalizeUrl', @sTmp0 OUT, 'http://www.chilkatsoft.com/index.htm'
    PRINT @sTmp0

    -- Output:
    -- http://chilkatsoft.com/
    -- http://chilkatsoft.com/purchase2.asp
    -- http://chilkatsoft.com/purchase2.asp
    -- http://chilkatsoft.com/purchase2.asp
    -- https://www.paypal.com/
    -- http://chilkatsoft.com/
    -- http://chilkatsoft.com/
    -- http://chilkatsoft.com/
    -- http://chilkatsoft.com/
    -- http://chilkatsoft.com

    EXEC @hr = sp_OADestroy @spider


END
GO