Extract Parts of an IPv4 Address
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown
Date: 17 Nov 2008
Description
This is another gem I could have done with knowing about years ago and was worth the effort of converting to a table-valued function
It utilizes the system function PARSENAME(), which is meant for extracting parts from a fully qualified 4-part object name.
The IP address, being a 4-part identifier, just fits nicely as an input to this fucntion, but this only works for IPV4 addresses.
There is some error checking built in to the function, which utilizes the udf_GetCountOfCharacterInString and udf_split2 functions, but this can easily be omitted.
Code
Function:
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
Test Code:
SELECT * FROM [dbo].[udf_ExtractPartsofIPAddressString] ('101.3.255.34')
SELECT * FROM [dbo].[udf_ExtractPartsofIPAddressString] ('3.255.34')
SELECT * FROM [dbo].[udf_ExtractPartsofIPAddressString] ('101,3.255.34')