Treeless Software SQL example returns Web Site name from a URL line

Returns Web Site name from a URL line.

USE [DB_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufs_URLSite] (
    @URL varchar(2000)
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE
        @Result varchar(2000),
        @SearchFirst varchar(100),
        @SearchNext varchar(100),
        @Pos int
    SET @Result = @URL
    SET @SearchNext = '/'
    -- **************
    -- Check for http
    -- **************
    SET @SearchFirst = 'http://'
    SET @Pos = CHARINDEX(@SearchFirst, @Result, 1)
    -- *********************
    -- Nope, check for https
    -- *********************
    IF @Pos = 0
        BEGIN
            SET @SearchFirst = 'https://'
            SET @Pos = CHARINDEX(@SearchFirst, @Result, 1)
        END
    -- *******************
    -- Nope, check for www
    -- *******************
    IF @Pos = 0
        BEGIN
            SET @SearchFirst = 'www.'
            SET @Pos = CHARINDEX(@SearchFirst, @Result, 1)
            -- ******************************************
            -- IF www then fudge with http in front of it
            -- ******************************************
            IF @Pos = 1
                BEGIN
                    SET @Result = 'http://' + @Result
                    SET @SearchFirst = 'http://' + @SearchFirst
                END
        END
    -- ***************
    -- Found something
    -- ***************
    IF @Pos = 1
        BEGIN
            SET @Pos = CHARINDEX(@SearchNext, @Result, LEN(@SearchFirst) + @Pos)
            IF @Pos > 1                                -- save up to next slash
                SET @Result = SUBSTRING(@Result, 1, @Pos - 1)
            -- ******************
            -- Do we have a query
            -- ******************
            SET @Pos = CHARINDEX('?', @Result, LEN(@SearchFirst) + 1)
            IF @Pos > 1                                -- remove the query
                SET @Result = SUBSTRING(@Result, 1, @Pos - 1)
            -- ************************
            -- Do we have a port number
            -- ************************
            SET @Pos = CHARINDEX(':', @Result, LEN(@SearchFirst) + 1)
            IF @Pos > 1                                -- remove the port
                SET @Result = SUBSTRING(@Result, 1, @Pos - 1)
        END
    -- *************
    -- Special Cases
    -- *************
    IF CHARINDEX('http://servername', @Result) > 0
        SET @Result = 'http://www.mycompany.com'
    -- ********************************************************
    -- We want to limit the URL LENgth to a specific max LENgth
    -- ********************************************************
    IF LEN(@Result) > 255
        SET @Result = SUBSTRING(@Result, 1, 255)
    RETURN @Result
END

Treeless Software

PC and Web Software Development.

Latest News

New VB.Net and SQL Examples

New Website Launched

July 7, 2011

Useful Links

Search