Ordering IP ranges in CIDR notation stored in a SQL DB correctly

gistfile1.md

Imagine you're storing IP ranges in CIDR notation in a database table like this:

CREATE TABLE ranges (
    block VARCHAR(50)
);

INSERT INTO ranges(block) VALUES ('192.168.2.0/24'),('192.168.10.0/24');

When you try to select the blocks and order them, you get:

SELECT block FROM ranges ORDER BY block;
+-----------------+
| block           |
+-----------------+
| 192.168.10.0/24 |
| 192.168.2.0/24  |
+-----------------+
2 rows in set (0.00 sec)

Here's a way to order them correctly:

SELECT block FROM ranges ORDER BY INET_ATON(SUBSTRING(block FROM LENGTH(block) * -1 FOR (INSTR(block, '/') - 1)));
+-----------------+
| block           |
+-----------------+
| 192.168.2.0/24  |
| 192.168.10.0/24 |
+-----------------+
2 rows in set (0.00 sec)