Monday, February 14, 2011

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)       

5 Comments:

Anonymous Anonymous said...

You saved my life! Thank you.

10:46 AM  
Anonymous Anonymous said...

Your sample query on 10.100.60.55 would return:
LowEnd 174341174
HighEnd 174341175

And if I run:
dbo.ConvertIPToLong('10.100.60.55')
That returns 174341175 (HighEnd)

Change your query to use 18.12.252.18/31 instead.
It returns:
LowEnd 302840850
HighEnd 302840851

But if I run dbo.ConvertIPToLong('18.12.252.18'), it returns 302840850 (LowEnd).

Doesn't look consistent.
Am I missing something?

11:06 AM  
Anonymous Anonymous said...

yes, your comment is a little in error I think. I think you meant
"Your sample query on 10.100.60.55/31 would return:
LowEnd 174341174
HighEnd 174341175"

That is true because the /31 designation returns both (there are only 2 with a /31 designation) IP addresses, and the .55 is the second (highest of the two.

With the .18/31 address the single .18 address is the first of the two.

Both are consistent within the CIDR range.

1:50 PM  
Blogger Connie said...

This was extremely helpful! Thank you.

10:59 AM  
Anonymous Anonymous said...

Lol. Why didn't you just say add INET_ATON to your database..

1:30 PM  

Post a Comment

<< Home