How to convert a CIDR IP address to a range in T-SQL
First pre-create this function in SQL Server (from http://www.stardeveloper.com).
CREATE FUNCTION [dbo].[ConvertIPToLong](@IP varchar(15))
RETURNS bigint
AS
BEGIN
DECLARE @Long bigint
SET @Long = CONVERT(bigint, PARSENAME(@IP, 4)) * 256 * 256 * 256 +
CONVERT(bigint, PARSENAME(@IP, 3)) * 256 * 256 +
CONVERT(bigint, PARSENAME(@IP, 2)) * 256 +
CONVERT(bigint, PARSENAME(@IP, 1))
RETURN (@Long)
END
This is a sample of T-SQL code I put together that will calculate the low and high IP ranges from a CIDR address. It's messy and I had to work around T-SQL's lack of bit shift operators.
Declare @CidrIP varchar(50)
Set @CidrIP = '10.100.60.55/28'
Select dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) as LowRange,
dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)