Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata