Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Ordering dotted decimal numbers

3:21 pm on Mar 18, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 18, 2008
votes: 0

is it possible (in sql or php) to order dotted decimal numbers properly. For example: IP address, or software versions.

this is the order you'd want

typical text ordering would produce

date ordering kind of uses the desired ordering principal where you order by the number before the first dash, then by the number before the second dash, etc.
3:36 pm on Mar 18, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 16, 2007
votes: 0

You could write a function that inserts zeros before sorting or place each octet in a separate field.
5:43 pm on Mar 18, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 18, 2008
votes: 0

using separate fields wouldn't work since you don't always know how many fields you'd have (like in versioning).

i guess adding zeros would work. you'd just have to add the same number of digits to every field.

that would sort properly i believe. I'd just never be able to go over 999. i think i'll play with this for a bit. Thanks for the suggestion.
10:21 am on Mar 19, 2010 (gmt 0)

New User

10+ Year Member

joined:May 31, 2003
posts: 38
votes: 0

Couldn’t you just remove the period character and pad it with "0"?


Would produce


And that I believe would sort correct if you sort numerically.
12:12 pm on Mar 22, 2010 (gmt 0)

Full Member

5+ Year Member

joined:Jan 4, 2007
votes: 0

Well, while this may be the DB forum, if you're happy (and it's practical) to do the sorting once it has been retrieved to your PHP script then take a look at usort [uk3.php.net] and code a custom dotted number comparison that compares them segment by segment.

Whether this is practical is going to depend on volume though.

Otherwise you may have to impose certain restrictions on what you store. For example, it's not uncommon to hexify IP address to an 8 "digit" hexadecimal number so that would become 7F000001, => 0A010203 etc. This being possible because you know how many segments and the maximum size of any segment.