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.
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')
|
 Updating... udf_ExtractPartsofIPAddressString.sql (2k) Andy Hughes, Jul 17, 2012, 11:32 AM
|
