Sort by IP Address

Perhaps It's just me, but It doesn't appear to know how sort IP addresses properly. For example

• 1.0.0.0 - 1.0.0.255
• 1.0.1.0 - 1.0.1.255
• 1.0.128.0 - 1.0.191.255
• 1.0.16.0 - 1.0.16.255

of course 1.0.16 should come before 1.0.128. That said if its not just me, please consider this for an enhancement request and thanks for all the handwork you've put into this, very nice product.

I suppose these IPs are stored in a text column. If so, the order is correct, as you can verifiy this way:

SELECT
    '1.0.0.0' < '1.0.0.255',
    '1.0.128.0' < '1.0.16.0',
    '128' < '16',
    '128' < '16.',

Ordering text is done by the character values, not by their numeric value. So "128" is lower than "16", even when taking the dot into account ("128" < "16.")

Thanks for the response. Yes, there in a text Column and I'm sure as such all is well. I'm just saying it would be nice if there were an option to tell it follow IP rules where the . separates the octets and Where 0.0.0.0 is the lowest and 255.255.255.255 is the highest possible values. Perhaps add a Sort as option when one clicks on the column header. Sort as Text, Sort as IP address or allow the user to enter a regular expression as the sort. Just some ideas. Thanks again for considering it. Have a great weekend!

The following link looked kind of interesting as an example of another app using regex to sort IP's

community.notepad-plus-plus.org/topic/14354/can-i-sort-ip-addresses-in-numeric-value

You mean in the data tab ?

I am thinking the "Add col" button on the sorting dropdown could get a dropdown menu, whith a new "Add expression" option, which then allows the user to enter text instead of just selecting a column. I'm not sure how a regular expression should look like for your particular use case, but I guess that's doable.

That or right below the Sort alphabetically check box you already have on the select columns. Sort custom. In looking closer at the notepad-plus-plus example the regex is used to change the format so the normal text sort can sort the entries. Then it is uses again to change the format back. That said its a multistep process so I don't think It can be achieved with just 1 regular expression statement. Just thinking on the fly here, you mentioned the user entering text instead of selecting a column.. if code isn't added to HS to sort IP addresses perhaps in the field we could refer to an external program. Thinking something like this:

1. HS could create a temp file with the contents of the column in question and call the external program with the file name as a parameter.
2. External program does sort and writes results back to file then exits with success or failure.
3. HS reads the temp file and displays the sorted column.

The rows could be passed as parameters but the number of rows would be limited, thus I'd think a file would provide a much greater number of rows to be sorted. Then the user could use their favorite language to open and sort. For example the perl script below does the trick ( I pieced this together from posts by Jean and Sean on stackoverflow )

#!/usr/bin/perl
use strict;
use warnings;
my $ip = $ARGV[0];

open my $handle, '<', $ip;
chomp(my @lines = <$handle>);
close $handle;

my @sorted =
    map substr($_, 4),
       sort
          map pack('C4a*', split(/\./), $_),
             @lines;

foreach (@sorted)
{
      print($_ . "\n");
}

Now my data is in the netrange format so I'd have to change the script to split and just use the 1st part, but still, I'm sure you get the idea. Id think creating that temp file in a .csv format would work where first column is the data and 2nd column is the row number. The above Perl script of course doesn't deal with that but its an easy change to have it write back to a csv format.

Well enough of my rambling for now, time for me head to work :-) and Thanks again.

Just as an FYI for others that may mind this thread later. I have a SQL query that I pieced tougher from matthewvince.com/2011/08/04/sorting-ip-addresses-in-mysql

As I store my data in ip - ip format from the attached image, I needed to split the two IP's apart, the first part splits on the - char and the 2nd part splits on the space that's after the 1st IP address. the INET_ATON turns the IP address in to a LONG so normal sort works on it. So I'll use that for now and cross my fingers IP sorting as added as a feature in the future.

select NetRange, SUBSTRING_INDEX(SUBSTRING_INDEX(NetRange,'-',1),' ',1)AS 'ipPart' FROM whois ORDER BY INET_ATON(ipPart);