Jasinski Technical Wiki

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

PoweredBy

Page History: CIDR IP Address Range - SQL Server

Compare Page Revisions



« 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.