Cool Tips‎ > ‎String Manipulation‎ > ‎

String Manipulation - 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')

ċ
udf_ExtractPartsofIPAddressString.sql
(2k)
Andy Hughes,
Jul 17, 2012, 11:32 AM
Comments