Calculer un masque de sous-réseau à partir d’un range d’adresses IPs
Une fonction en T-SQL retournant le masque de sous-réseau (netmask) à partir de la première et dernière IP du range… Compatible MS SQL Server 2000.
Ci-dessous le code de la fonction calculant le masque:
create function getSubnetMask (@RangeIPStart as varchar(50), @RangeIPStop as varchar(50))
returns varchar(50)
as
-- Procedure Name : getSubnetMask
-- Created On : 22.01.2008
-- Dependencies: user-defined function SplitWords
begin
declare @Value as int
declare @SubnetMask as varchar(50)
set @SubnetMask = ''
DECLARE SubnetMask CURSOR FAST_FORWARD FOR
SELECT CASE WHEN (CONVERT(INT,IP1.[value]) = CONVERT(INT,IP2.[value]))
THEN 255
ELSE 255 - ((CONVERT(INT,IP1.[value]) ^ CONVERT(INT,IP2.[value])))
END AS value
FROM (SELECT * FROM [dbo].[SplitWords] (@RangeIPStart)) AS IP1
INNER JOIN (SELECT * FROM [dbo].[SplitWords] (@RangeIPStop)) AS IP2 ON IP1.pos = IP2.pos
OPEN SubnetMask
FETCH NEXT FROM SubnetMask INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
if @SubnetMask <> ''
begin
set @SubnetMask = @SubnetMask+'.'
end
set @SubnetMask = @SubnetMask+CAST(@Value as varchar)
FETCH NEXT FROM SubnetMask INTO @Value
END
CLOSE SubnetMask
DEALLOCATE SubnetMask
return @SubnetMask
end
Cette dernière est dépendante de la fonction user-defined SplitWords… Le code:
CREATE FUNCTION [dbo].[SplitWords](@text varchar(8000))
RETURNS @words TABLE (pos smallint primary key, value varchar(8000))
AS
BEGIN
DECLARE @pos smallint,
@i smallint,
@j smallint,
@count smallint,
@s varchar(8000)
SET @pos = 1
SET @count = 0
WHILE @pos <= LEN(@text)
BEGIN
SET @i = CHARINDEX(' ', @text, @pos)
SET @j = CHARINDEX('.', @text, @pos)
IF @i > 0 OR @j > 0
BEGIN
IF @i = 0 OR (@j > 0 AND @j < @i)
SET @i = @j
IF @i > @pos
BEGIN
-- @i now holds the earliest delimiter in the string
SET @s = SUBSTRING(@text, @pos, @i - @pos)
INSERT INTO @words
VALUES (@count, @s)
SET @count = @count + 1
END
SET @pos = @i + 1
WHILE @pos < LEN(@text) AND SUBSTRING(@text, @pos, 1) IN (' ', ',')
SET @pos = @pos + 1
END
ELSE
BEGIN
INSERT INTO @words
VALUES (@count, SUBSTRING(@text, @pos, LEN(@text) - @pos + 1))
SET @pos = LEN(@text) + 1
END
END
RETURN
END
