MySQL query to convert CIDR into IP range

I have a table which contains a column as CIDR values like 1.0.85.128/25, I have 2 other columns (start_ip_range and end_ip_range), I want to populate those 2 column FROM CIDR column

Java code might look like as follows:

String[] parts = cidr.split("/"); String ip = parts[0]; int prefix; if (parts.length < 2) {     prefix = 0; } else {     prefix = Integer.parseInt(parts[1]); }  String[] ipParts = ip.split("\\."); int address = ((new Integer(ipParts[0]) << 24) & 0xFF000000)      | ((new Integer(ipParts[1]) << 16) & 0xFF0000)      | ((new Integer(ipParts[2]) << 8) & 0xFF00)      | (new Integer(ipParts[3]) & 0xFF); int mask = (-1) << (32 - prefix); int start = address & mask; int end = start + (~mask); 

How this can be transformed into MySql Query.

Add Comment
1 Answer(s)

You can it convert so

Query

SELECT   INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1))     & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(val, '/', -1))  ) -1 )) from_ip,   INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1))     | ((0x100000000 >> SUBSTRING_INDEX(val, '/', -1) ) -1 )) to_ip FROM cidr; 

Sample Table

CREATE TABLE `cidr` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `val` varchar(32) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  INSERT INTO `cidr` (`id`, `val`) VALUES     (1, '192.168.2.12/24'),     (2, '192.168.2.12/25'),     (3, '1.0.85.128/25'),     (4, '192.168.2.12/32'); 

Or you can use it in virtual Columns. So you get direct the correct Value.

Virtual Fields

CREATE TABLE `cidr1` (   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,   `val` VARCHAR(32) DEFAULT NULL,   `from_ip` VARCHAR(15) AS ( INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1)) & 0xffffffff ^ ((0x1 << ( 32 - SUBSTRING_INDEX(val, '/', -1))  ) -1 ))) PERSISTENT ,   `to_ip` VARCHAR(15)  AS ( INET_NTOA(INET_ATON( SUBSTRING_INDEX(val, '/', 1)) | ((0x100000000 >> SUBSTRING_INDEX(val, '/', -1) ) -1 ))) PERSISTENT ,   PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;  INSERT INTO `cidr1` (`id`, `val`) VALUES     (1, '192.168.2.12/24'),     (2, '192.168.2.12/25'),     (3, '1.0.85.128/25'),     (4, '192.168.2.12/32');   MariaDB []> select * from cidr1; +----+-----------------+--------------+---------------+ | id | val             | from_ip      | to_ip         | +----+-----------------+--------------+---------------+ |  1 | 192.168.2.12/24 | 192.168.2.0  | 192.168.2.255 | |  2 | 192.168.2.12/25 | 192.168.2.0  | 192.168.2.127 | |  3 | 1.0.85.128/25   | 1.0.85.128   | 1.0.85.255    | |  4 | 192.168.2.12/32 | 192.168.2.12 | 192.168.2.12  | +----+-----------------+--------------+---------------+ 4 rows in set (0.00 sec)  MariaDB []> 
Answered on August 30, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.