Sorting Dotted-Quad IP Values in Numeric Order
Problem
You want to sort strings that represent IP numbers in numeric order.
Solution
Break apart the strings and sort the pieces numerically. Or just use
INET_ATON( )
.
Discussion
If a table contains IP numbers represented as strings in dotted-quad
notation (for example, 111.122.133.144
),
they’ll sort lexically rather than numerically. To
produce a numeric ordering instead, you can sort them as four-part
values with each part sorted numerically. To accomplish this, use a
technique similar to that for sorting hostnames, but with the
following differences:
Dotted quads always have four segments, so there’s no need to prepend dots to the value before extracting substrings.
Dotted quads sort left to right, so the order in which substrings are used in the
ORDER
BY
clause is opposite to that used for hostname sorting.The segments of dotted-quad values are numbers, so add zero to each substring to tell MySQL to using a numeric sort rather than a lexical one.
Suppose you have a hostip
table with a
string-valued ip
column containing IP numbers:
mysql> SELECT ip FROM hostip ORDER BY ip;
+-----------------+
| ip |
+-----------------+
| 127.0.0.1 |
| 192.168.0.10 |
| 192.168.0.2 |
| 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, you can extract each
segment and add zero to convert it to a number using an
ORDER
BY
clause ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.