IPStringToNumber

USE [CM_Supplemental]

 GO

/****** Object: UserDefinedFunction [dbo].[IPStringToNumber] Script Date: 7/22/2016 8:36:28 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s