homepage Welcome to WebmasterWorld Guest from 54.198.148.191
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Ordering dotted decimal numbers
noyearzero




msg:4100389
 3:21 pm on Mar 18, 2010 (gmt 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

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




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

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

noyearzero




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

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




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

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




msg:4102257
 12:12 pm on Mar 22, 2010 (gmt 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 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved