Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

CIDR IP Address Range - SQL Server

Overview¶

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

Dependencies¶

Both versions of the function below depend on the dbo.ConvertIpToLong and dbo.ConvertLongToIp functions found on this page: IP Address Conversion Between Integer and String - SQL Server.

Code¶

This version takes an IP address and Mask as parameters

create function [dbo].[GetCidrIpAddressRange](@IpAddress varchar(15), @Mask int)
returns @result table 
(
    LowRange    varchar(15) not null,
    HighRange   varchar(15) not null,
    AddressQty  bigint not null
)
as

begin

declare @Base       bigint  = cast(4294967295 as bigint)
declare @Power      bigint  = Power(2.0, 32.0 - @Mask) - 1
declare @LowRange   bigint  = dbo.ConvertIPToLong(@IpAddress) & (@Base ^ @Power)
declare @HighRange  bigint  = @LowRange + @Power

insert @result
select
     LowRange   = dbo.ConvertLongToIp(@LowRange)
    ,HighRange  = dbo.ConvertLongToIp(@HighRange)
    ,AddressQty = convert(bigint, power(2.0, (32.0 - @Mask)))

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 
(
    LowRange    varchar(15) not null,
    HighRange   varchar(15) not null,
    AddressQty  bigint 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.0, 32.0 - @Mask) - 1
declare @LowRange   bigint  = dbo.ConvertIPToLong(@IpAddress) & (@Base ^ @Power)
declare @HighRange  bigint  = @LowRange + @Power

insert @result
select
     LowRange   = dbo.ConvertLongToIp(@LowRange)
    ,HighRange  = dbo.ConvertLongToIp(@HighRange)
    ,AddressQty = convert(bigint, power(2.0, (32.0 - @Mask)))

return
end