Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a column in Sharepoint containing IP-addresses. I can't figure out how to sort them correctly.

They are being sorted like this:

192.168.1.1

192.168.1.10

192.168.1.100

192.168.1.2

Rather than

192.168.1.1

192.168.1.2

192.168.1.10

192.168.1.100

The only "solution" I can find was this thread:

http://social.technet.microsoft.com/Forums/sv-SE/sharepointgeneral/thread/c0902e45-0427-4081-b64a-b41ecaecf900

However, I have tried the formula suggestions and they simply don't work correctly. I have tried by using two columns with a formula each and by using one column with the combined formula:

=LEFT([Internal IP];7)&RIGHT([Internal IP];3)

It seems silly to me that sorting numbers would be such a hassle. Have I missed something?

share|improve this question
1  
The sort is not considering numbers, but a piece of text (string). So, it's behaving as expected. –  Dante Jan 11 '11 at 12:49
    
The only thing I can think of is to insert a, b or c in front of each number based on if it's 0 to 9, 10 to 99 or 100 to 255 and then sort it that way. It still seems like a lot of hassle though. Besides if I theoretically wanted an ip like "10.10.10.x" it would fail my first sort rule because it has less numbers than 192.168.1.x. That Microsoft thread is hardly a solution even if the code had sorted the IP's correctly, because it doesn't accept adding an IP with a different length before the last octet. –  jman Jan 11 '11 at 13:57

2 Answers 2

up vote 1 down vote accepted

This is actually pretty easy, I've done this in SQL Server for a long time. You just need to stop thinking of the data as an IP address. There is an old trick called bit twiddling that can be used to convert the value to an integer first. For example, 192.168.0.1 becomes 3232235521. Integers are fast and easy to sort. After doing the conversion, you drop the integer into a hidden computed column and do the sort on that column.

So if you have a text column called IPAddress and a calculated field called Integer, you would add this formula to compute the Integer column - it's really just a little binary math and you only end up storing an additional Int32 by treating each octet as a byte:

=VALUE(IF(NOT(ISBLANK([IPAddress]));(LEFT([IPAddress];
FIND(".";[IPAddress];1)-1)*16777216)+(MID([IPAddress];
FIND(".";[IPAddress];1)+1;FIND(".";[IPAddress];
FIND(".";[IPAddress];1)+1)-FIND(".";[IPAddress];1)-1)*65536)+(MID([IPAddress];
FIND(".";[IPAddress];FIND(".";[IPAddress];1)+1)+1;
FIND(".";[IPAddress];FIND(".";[IPAddress];
FIND(".";[IPAddress];1)+1)+1)-FIND(".";[IPAddress];
FIND(".";[IPAddress];1)+1)-1)*256)+(RIGHT([IPAddress];
LEN([IPAddress])-FIND(".";[IPAddress];
FIND(".";[IPAddress];FIND(".";[IPAddress];1)+1)+1)));0))

Then create a view that sorts by the hidden calcualted field Integer. If you need to sort IPv6 addresses, the concept is the same, but you're converting from hex values to binary to integers and you'll use more storage.

share|improve this answer
    
Wow. Thanks a lot Joshua. This was really useful! Works like a charm. –  jman Jan 12 '11 at 8:43

If Joshua' formula doesn't quite work for you, you may need to substitute all the semicolons for commas.

=VALUE(IF(NOT(ISBLANK([IPAddress])),(LEFT([IPAddress],
 FIND(".",[IPAddress],1)-1)*16777216)+(MID([IPAddress],
 FIND(".",[IPAddress],1)+1,FIND(".",[IPAddress],
 FIND(".",[IPAddress],1)+1)-FIND(".",[IPAddress],1)-1)*65536)+
 (MID([IPAddress],FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)+1,
 FIND(".",[IPAddress],FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)+1)-
 FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)-1)*256)+(RIGHT([IPAddress],
 LEN([IPAddress])-FIND(".",[IPAddress],FIND(".",[IPAddress],
 FIND(".",[IPAddress],1)+1)+1))),0))

The above is formatted for clarity and normally entered on one line.

share|improve this answer
    
Thanks for pointing this out. The semicolons worked for me but I think it's language dependant. –  jman Jul 5 '11 at 8:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.