Using SQL to determine cidr value of a subnet mask

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

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));

e.g. you need to convert 255.255.255.252 netmask into bit mask.

I always use this simple query (PostgreSQL) :

SELECT 32-length(trim(((split_part('255.255.255.252','.',1)::bigint*(256^3)::bigint +                        split_part('255.255.255.252','.',2)::bigint*(256^2)::bigint +                        split_part('255.255.255.252','.',3)::bigint*256 +                        split_part('255.255.255.252','.',4)::bigint)::bit(32))::text,'1'));

not as nice as could be, but it's short and working like a charm..