-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE FUNCTION ippart(@ip VARCHAR(16), @n INTEGER) RETURNS VARCHAR(16)
AS
BEGIN
    DECLARE @ix INTEGER
    DECLARE @newix INTEGER
    DECLARE @s VARCHAR(4)
    DECLARE @i INTEGER
    SET @ix = 1
    SET @i = 0
    WHILE @i < @n
    BEGIN
        IF @ix <= LEN(@ip)
        BEGIN
            SET @newix = CHARINDEX('.', @ip, @ix)
            IF @newix = 0 SET @newix = LEN(@ip) + 1
            SET @s = SUBSTRING(@ip, @ix, @newix-@ix)
            SET @ix = @newix + 1
        END
        ELSE
            SET @s = ''
        SET @i = @i + 1
    END
    IF @s = '' SET @s = NULL
    RETURN @s
END
GO

CREATE FUNCTION iprepl(@ipaddr VARCHAR(16), @ipseg VARCHAR(16)) RETURNS VARCHAR(16)
AS
BEGIN
    DECLARE @ipaddr_1 VARCHAR(16)
    DECLARE @ipaddr_2 VARCHAR(16)
    DECLARE @ipaddr_3 VARCHAR(16)
    DECLARE @ipaddr_4 VARCHAR(16)
    DECLARE @ipseg_1 VARCHAR(16)
    DECLARE @ipseg_2 VARCHAR(16)
    DECLARE @ipseg_3 VARCHAR(16)
    DECLARE @ipseg_4 VARCHAR(16)
    SET @ipaddr_1 = dbo.ippart(@ipaddr, 1)
    SET @ipaddr_2 = dbo.ippart(@ipaddr, 2)
    SET @ipaddr_3 = dbo.ippart(@ipaddr, 3)
    SET @ipaddr_4 = dbo.ippart(@ipaddr, 4)
    SET @ipseg_1 = dbo.ippart(@ipseg, 1)
    SET @ipseg_2 = dbo.ippart(@ipseg, 2)
    SET @ipseg_3 = dbo.ippart(@ipseg, 3)
    SET @ipseg_4 = dbo.ippart(@ipseg, 4)
    RETURN COALESCE(@ipseg_1, @ipaddr_1) + '.' + COALESCE(@ipseg_2, @ipaddr_2) + '.' + COALESCE(@ipseg_3, @ipaddr_3) + '.' + COALESCE(@ipseg_4, @ipaddr_4)
END
GO

DECLARE @ipseg VARCHAR(16)
DECLARE @ipaddr VARCHAR(16)
SET @IPSEG = '1.2.'
SET @IPADDR = '1.7.8.9'
PRINT @IPSEG
PRINT @IPADDR
PRINT dbo.iprepl(@ipaddr, @ipseg)
SET @IPSEG = '1.'
SET @IPADDR = '1.7.8.9'
PRINT @IPSEG
PRINT @IPADDR
PRINT dbo.iprepl(@ipaddr, @ipseg)
SET @IPSEG = '1.2.3.'
SET @IPADDR = '1.7.8.9'
PRINT @IPSEG
PRINT @IPADDR
PRINT dbo.iprepl(@ipaddr, @ipseg)
GO

DROP FUNCTION ippart
DROP FUNCTION iprepl
GO
