How to Sort IP address 3

[rleyba828]

Hi Team,

I have been searching through the forums here and looking for a formula that will correctly sort IP addresses? Perhaps someone has done this before? As you can see, the default sort for a list of IPs below will be incorrectly sorted. Thanks in advance.

--robert

P.S. I am using version XI.

Sorted IPs (notice it is incorrect).
10.0.1.3
10.0.1.61
10.0.1.62
10.0.10.3
10.0.117.4
10.0.117.5
10.0.118.4
10.0.118.5
10.0.12.6
10.0.13.3

 

[lbass]

How do you think it should appear when sorted?

-LB

 

[Turkbear]

Hi,
If you mean to sort in numerical order ( so that 117 and 118 for instance are after 13) then try this:
there is probably an easier way, but..

Create formulas to isolate the sub net numbers:
@Ipfirst
ToNumber(split({IPFIELD},".")[1])

@IpSecond
ToNumber(split({IPFIELD},".")[2])

@IpThirdSub
ToNumber9split({IPFIELD},".")[3]

@IpEndSub
ToNumber(split({IPFIELD},".")[4])

Then sort by those formulas






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rleyba828]

Thanks Guys!

The addressing scheme for IP addresses is the 4 Octet method (separated by "." as below), where the leftmost octet is the most significant and the rightmost is the least. They are to be sorted numerically within octets.

applying to the case below, the CORRECT sort order would be:

Sorted IPs (CORRECT ORDER)
10.0.1.3
10.0.1.61
10.0.1.62
10.0.10.3
10.0.12.6
10.0.13.3
10.0.117.4
10.0.117.5
10.0.118.4
10.0.118.5

------------------------------------

Sorted IPs (notice it is incorrect).
10.0.1.3
10.0.1.61
10.0.1.62
10.0.10.3
10.0.117.4
10.0.117.5
10.0.118.4
10.0.118.5
10.0.12.6
10.0.13.3


TurkBear, I am trying to dissect your solution, I'll try it out, but if there is a "function" I can define to do those steps all in one go, then that would be easiest for me. Thanks very much.

 

[Turkbear]

Hi,
since you need to sort each piece numerically, I am not sure that is one function that would handle that.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could try something like this:

stringvar array x := split({table.IP},".");
stringvar y :=
totext(val(x[1]),"000")+"."+
totext(val(x[2]),"000")+"."+
totext(val(x[3]),"000")+"."+
totext(val(x[4]),"000");
y

Use this as your sort field, but use the original field for display on the report.

-LB

 

[jdemmi]

LOL...Lbass, I was about to post a very similar approach....I previewed my post, found an error and when I edited I saw yours..

Anywho...

Create a formula...

StringVar Array x := split({YourTable.Field},".");
Right("000"&x[1],3) & Right("000"&x[2],3) & Right("000"&x[3],3) & Right("000"&x[4],3)

Sort on this formula....

-- Jason
"It's Just Ones and Zeros

 

[rleyba828]

LB and Jdemmi,

Thanks! This one off formula is the sort of thing I was looking for. I will try this out tomorrow after I download my 2,000 IP address xls file. This would save me tons of work!

--robert