-- This script converts string-based IP addresses to their numeric equivalents so that it is easy to do arithmatic on them such as find an IP address that is within a range.
--
-- NOTE: This function calls the split function which is included in the po

IF exists (SELECT * from dbo.sysobjects
    WHERE id = object_id(N'[dbo].[IPStringToNumber]')
    AND OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
DROP FUNCTION [dbo].[IPStringToNumber]
GO

CREATE FUNCTION dbo.IPStringToNumber( @vcIPAddress varchar(15))
/**************************************************************************
DESCRIPTION: Returns Numeric IP, otherwise returns null

PARAMETERS:
        @vcIPAddress    - The string containing a valid IP
       
RETURNS:    IP converted to bigint or null if not a valid IP
       
USAGE:         SELECT  dbo.IPStringToNumber( '10.255.255.255')

AUTHOR:    Karen Gayda

DATE:     06/11/2003

MODIFICATION HISTORY:
    WHO        DATE        DESCRIPTION
    ---        ----------    ---------------------------------------------------

***************************************************************************/

    RETURNS bigint
AS
BEGIN
    DECLARE   
        @biOctetA     bigint,
        @biOctetB    bigint,
        @biOctetC    bigint,
        @biOctetD    bigint,
        @biIP            bigint

    DECLARE @tblArray TABLE
       (
        OctetID        smallint,          --Array index
           Octet        bigint               --Array element contents
       )

    --split the IP string and insert each octet into a table row
    INSERT INTO @tblArray
    SELECT ElementID, Convert(bigint,Element) FROM dbo.Split(@vcIPAddress, '.')
   
    --check that there are four octets and that they are within valid ranges
    IF (SELECT COUNT(*) FROM @tblArray WHERE Octet BETWEEN 0 AND 255) = 4
    BEGIN
        SET @biOctetA = (SELECT (Octet * 256 * 256 * 256) FROM @tblArray WHERE OctetID = 1)
        SET @biOctetB = (SELECT (Octet * 256 * 256 ) FROM @tblArray WHERE OctetID = 2)
        SET @biOctetC = (SELECT (Octet * 256 ) FROM @tblArray WHERE OctetID = 3)
        SET @biOctetD = (SELECT (Octet) FROM @tblArray WHERE OctetID = 4)
        SET @biIP = @biOctetA + @biOctetB + @biOctetC + @biOctetD
    END
       
    RETURN(@biIP)
END

IF exists (SELECT * from dbo.sysobjects
    WHERE id = object_id(N'[dbo].[Split]')
    AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[Split]
GO

GO
CREATE FUNCTION dbo.Split
 (   
    @vcDelimitedString         varchar(8000),
    @vcDelimiter            varchar(100)
)
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
        delimiter points.  Returns the individual items as a table data
        type with the ElementID field as the array index and the Element
        field as the data

PARAMETERS:
        @vcDelimitedString        - The string to be split
        @vcDelimiter            - String containing the delimiter where
                            delimited string should be split

RETURNS:
        Table data type containing array of strings that were split with
        the delimiters removed from the source string

USAGE:
        SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

AUTHOR:    Karen Gayda

DATE:     05/31/2001

MODIFICATION HISTORY:
    WHO        DATE        DESCRIPTION
    ---        ----------    ---------------------------------------------------

***************************************************************************/
RETURNS @tblArray TABLE
   (
    ElementID    smallint    IDENTITY(1,1),  --Array index
       Element        varchar(1000)            --Array element contents
   )
AS
BEGIN

    DECLARE
    @siIndex                    smallint,
    @siStart                    smallint,
    @siDelSize                    smallint

    SET @siDelSize    = LEN(@vcDelimiter)
    --loop through source string and add elements to destination table array
    WHILE LEN(@vcDelimitedString) > 0
    BEGIN
        SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
        IF @siIndex = 0
        BEGIN
            INSERT INTO @tblArray VALUES(@vcDelimitedString)
            BREAK
        END
        ELSE
        BEGIN
            INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
            SET @siStart = @siIndex + @siDelSize
            SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
        END
    END
   
    RETURN
END
GO