Database Reference
In-Depth Information
| 192.168.1.10 |
| 192.168.1.2 |
| 21.0.0.1 |
| 255.255.255.255 |
+-----------------+
The preceding query produces output sorted in lexical order. To sort the
ip
values
numerically, extract each segment and add zero to convert it to a number like this:
mysql>
SELECT ip FROM hostip
->
ORDER BY
->
SUBSTRING_INDEX(ip,'.',1)+0,
->
SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-3),'.',1)+0,
->
SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1)+0,
->
SUBSTRING_INDEX(ip,'.',-1)+0;
+-----------------+
| ip |
+-----------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+-----------------+
However, although that
ORDER
BY
clause produces a correct result, it's complicated. A
simpler solution uses the
INET_ATON()
function to convert network addresses in string
form to their underlying numeric values, then sorts those numbers:
mysql>
SELECT ip FROM hostip ORDER BY INET_ATON(ip);
+-----------------+
| ip |
+-----------------+
| 21.0.0.1 |
| 127.0.0.1 |
| 192.168.0.2 |
| 192.168.0.10 |
| 192.168.1.2 |
| 192.168.1.10 |
| 255.255.255.255 |
+-----------------+
If you're tempted to sort by simply adding zero to the
ip
value and using
ORDER
BY
on
the result, consider the values that kind of string-to-number conversion actually pro‐
duces:
mysql>
SELECT ip, ip+0 FROM hostip;
+-----------------+---------+
| ip | ip+0 |
+-----------------+---------+
| 127.0.0.1 | 127 |
