Page History: CIDR IP Address Range - SQL Server

Compare Page Revisions

5 4 3 2 1 0 Current 5 4 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Tue, Dec 29, 2015, 11:39 AM

Overview¶

This article provides the code for a SQL Server table-valued function to convert a CIDR to its equivalent range of IP addresses.

Code¶

This version takes an IP address and Mask as parameters

create function dbo.GetCidrIpAddressRange(@IpAddress varchar(15), @Mask int)
returns @result table 
(
    -- columns returned by the function
    LowRange varchar(15) NOT NULL,
    HiRange varchar(15) NOT NULL
)
as

begin

/*
declare @Cidr varchar(50) = '10.100.60.55/28'
*/

declare
     @Base      bigint      = cast(4294967295 as bigint)
    --,@IpAddress varchar(39) = left(@Cidr, patindex('%/%' , @Cidr) - 1)
    --,@Mask      int         = Cast(substring(@Cidr, patindex('%/%' , @Cidr) + 1, 2) as int)

declare @Power      bigint  = Power(2, 32 - @Mask) - 1
declare @LowRange   bigint  = dbo.ConvertIPToLong(@IpAddress) & (@Base ^ @Power)
declare @HiRange    bigint  = @LowRange + @Power

insert @result
select
     LowRange   = dbo.ConvertLongToIp(@LowRange)
    ,HighRange  = dbo.ConvertLongToIp(@HiRange)

   return
end

This version takes the CIDR itself and parses out the IP address and mask internally.

create function dbo.GetCidrIpAddressRangeAlt(@Cidr varchar(50))
returns @result table 
(
    -- columns returned by the function
    LowRange varchar(15) NOT NULL,
    HiRange varchar(15) NOT NULL
)
as

begin

/*--- Begin Testing ---*/
/*
declare @Cidr varchar(50) = '10.100.60.55/28'
*/
/*--- End Testing ---*/
declare
     @Base      bigint      = cast(4294967295 as bigint)
    ,@IpAddress varchar(39) = left(@Cidr, patindex('%/%' , @Cidr) - 1)
    ,@Mask      int         = Cast(substring(@Cidr, patindex('%/%' , @Cidr) + 1, 2) as int)

declare @Power      bigint  = Power(2, 32 - @Mask) - 1
declare @LowRange   bigint  = dbo.ConvertIPToLong(@IpAddress) & (@Base ^ @Power)
declare @HiRange    bigint  = @LowRange + @Power

insert @result
select
     LowRange   = dbo.ConvertLongToIp(@LowRange)
    ,HighRange  = dbo.ConvertLongToIp(@HiRange)

   return
end