Convert IP address to numeric equivalent

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:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/
RETURNS bigint
AS
BEGIN
DECLARE
@biOctetA bigint,
@biOctetBbigint,
@biOctetCbigint,
@biOctetDbigint,
@biIP    bigint

DECLARE @tblArray TABLE 
   (
OctetIDsmallint,  --Array index
   Octetbigint   --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),
@vcDelimitervarchar(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:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/RETURNS @tblArray TABLE 
   (
ElementIDsmallintIDENTITY(1,1),  --Array index
   Elementvarchar(1000)--Array element contents
   )
AS
BEGIN

DECLARE 
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint


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