CODEWITHSUNDEEP
mysqlip-address
Santosh Pillai
Santosh Pillai

Reputation: 1391

Calculate subnet from IP Address and Mask using mysql query

Is it possible to calculate the subnet if I have a IP address and mask using MySQL? I know to do this in PHP, but I am looking for a way to do this in MySQL.

For example, given the IP address 10.196.73.249/29 I'd like to return the network address of 10.196.73.248.

Upvotes: 1

Views: 4906

Answers (1)

miken32
miken32

Reputation: 42712

If you already know how to do this in PHP, then the math shouldn't be much of a surprise for you, just IP address AND subnet mask.

Use MySQL's INET_ATON() function to get the IP address into a number; this is equivalent to PHP's ip2long() function. To create the mask, use the REPEAT() function to put the requisite number of ones, and also to fill the rest of the string with zeroes. CONV() changes that binary string to decimal so we can do the math, and then a final INET_NTOA() to get it back to IP address form.

SET @IP='10.196.73.249';
SET @CIDR='29';
SELECT CONV(CONCAT(REPEAT(1, @CIDR), REPEAT(0, 32 - @CIDR)), 2, 10) INTO @MASK;
SELECT @IP AS ip,
    INET_NTOA(@MASK) AS mask,
    INET_NTOA(INET_ATON(@IP) & @MASK) AS network;

Here is how to implement as a stored program:

CREATE FUNCTION get_network (ipadd VARCHAR(15), mask VARCHAR(2)) RETURNS VARCHAR(15)
RETURN INET_NTOA(
    INET_ATON(ipadd) &
    CONV(CONCAT(REPEAT(1, mask), REPEAT(0, 32 - mask)), 2, 10)
);

This can be called like so:

root@localhost [test]> SELECT get_network("192.168.242.234", "20");
+--------------------------------------+
| get_network("192.168.242.234", "20") |
+--------------------------------------+
| 192.168.240.0                        |
+--------------------------------------+
1 row in set (0.00 sec)

Upvotes: 5

Related Questions