<p>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.</p>

<p>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.</p>

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

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

SQL queries don't have a procedural looping construct (notwithstanding procedural language), but you can compare one set of rows to another set of rows, which is kind of like a loop.
You only have 32 possible subnet masks.  In cases like this, it makes sense to create a small table that stores these 32 masks and the associated CIDR number.
```
```
CREATE TABLE cidr (
  bits INT UNSIGNED PRIMARY KEY,
  mask INT UNSIGNED NOT NULL
);

INSERT INTO cidr (bits) VALUES
  ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9), (10),
  (11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
  (21), (22), (23), (24), (25), (26), (27), (28), (29), (30),
  (31), (32);

UPDATE cidr SET mask = ((POWER(2,32)-1)<<(32-bits)) & (POWER(2,32)-1);

SELECT CONCAT(s.ip_addr, '/', c.bits)
FROM cidr c JOIN subnets s ON (c.mask = inet_aton(s.ip_mask));
```
```