Ordering dotted decimal numbers

[noyearzero]

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

2.1.3.4
10.0.0.3
10.0.0.4

typical text ordering would produce

10.0.0.3
10.0.0.4
2.1.3.4

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.

[caribguy]

You could write a function that inserts zeros before sorting or place each octet in a separate field.

[noyearzero]

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

1.0.4.1
2.0
3.3.0

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

001.000.004.001
002.000
003.003.000

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.

[runarb]

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

Then
1.0.4.1
2.0
3.3.0
3.4.9

Would produce

10410000
20000000
33000000
34900000


And that I believe would sort correct if you sort numerically.

[Status_203]

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 127.0.0.1 would become 7F000001, 10.1.2.3 => 0A010203 etc. This being possible because you know how many segments and the maximum size of any segment.