Using SQL to determine cidr value of a subnet mask

sql subnet bitmask cidr
Matt P picture Matt P · Nov 10, 2008 · Viewed 11.4k times · Source

I'd like to find a way to do a SQL query that will calculate the cidr (bit representation) of a subnet mask stored in the database. So for example, I've got either 255.255.255.0 or its decimal value (4294967040) stored in the database. I'd like to do a select and get back /24 representation via the query.

I've done things like the following to determine the last IP of a subnet so I'm hoping to do something similar to determine the cidr representation of a mask.

select concat(inet_ntoa(ip_addr),'-',
    inet_ntoa(ip_addr+(POWER(2,32)-ip_mask-1))) range 
from subnets 
order by ip_addr

Preferably this would be a SQL statement that would work under mysql, postgres, oracle etc.

Answer

Matt P picture Matt P · Dec 30, 2008

I think I have found the solution to my issue. Here is what I have done:

select CONCAT(INET_NTOA(ip_addr),'/',32-log2((4294967296-ip_mask))) net 
from subnets 
order by ip_addr

Basically I take my decmial mask and subtract it from the maximum decimal value. I then to a log2 on that value to get the logarithm value. Then simply subtract that from 32 (the maximum bit available).

Hope that helps others.

Thanks

Related questions

JavaScript: Is IP In One Of These Subnets?

So I have ~12600 subnets: eg. 123.123.208.0/20 and an IP. I can use a SQLite Database or an array or whatever There was a similar question asked about a month ago, however I am not looking for checking one IP against one …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More