Extract Parts of an IPv4 Address

DROP FUNCTION dbo.[udf_ExtractPartsofIPAddressString]

GO

CREATE FUNCTION [dbo].[udf_ExtractPartsofIPAddressString]

(

@IPString VARCHAR(15) -- the IP string to search (IPV4 only)

)

RETURNS @Results TABLE

(

A INT,

B INT,

C INT,

D INT

)

AS

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

Purpose: Splits a correctly formatted IP address string

(1.1.1.1) into component parts.

Error checking for correctness (4 integers delimited by '.')

is carried out. An error causes -1 to be returned in all parts

This will only work for a corerctly formet IPV4 address

as it utilises the system function PARSENAME, which is designed

to be used to extract parts of a 4-part object name

Author: Unknown

History 17 Sep 2008 - Initial Issue

17 Jul 2012 - converted to TVF and added error checking

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

BEGIN

-- error checking

-- first, check that there are 3 correct delimiters

IF dbo.udf_GetCountOfCharacterInString('.', @IPString, 0) = 3

-- then check that there are 4 distinct values

AND (SELECT COUNT(*) FROM dbo.udf_split2 (@IPString, '.')) = 4

BEGIN

INSERT @Results (A, B, C, D)

SELECT PARSENAME(IP, 4) AS "A"

PARSENAME(IP, 3) AS "B"

PARSENAME(IP, 2) AS "C"

PARSENAME(IP, 1) AS "D" FROM (SELECT REPLACE(@IPString, ', ', '.')) D(IP)

END

ELSE

BEGIN

INSERT @Results (A, B, C, D)

VALUES (-1, -1, -1, -1)

END

RETURN

END