Welcome to WebmasterWorld Guest from 54.196.238.210

Forum Moderators: open

Message Too Old, No Replies

Ordering dotted decimal numbers

     

noyearzero

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

5+ Year Member



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

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

WebmasterWorld Senior Member 5+ Year Member



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

noyearzero

5:43 pm on Mar 18, 2010 (gmt 0)

5+ Year Member



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

10:21 am on Mar 19, 2010 (gmt 0)

10+ Year Member



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

12:12 pm on Mar 22, 2010 (gmt 0)

5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month