Can I sort IP addresses in numeric value
-
Hello,
I have a problem, I need to find a way to sort ip addresses in the following format:
192.168.1.1
192.168.1.2
192.168.2.12
192.168.2.34
192.168.3.4This is just a small example I am moving the data into notepad ++ in a jumbled format i.e
192.168.2.3
172.10.10.3
192.168.1.56Has anyone come across this problem and can help please?
-
Hi, @mark-brookman,
Not very difficult, indeed ! We just have to re-write each IPV4 address, in such a way that it only needs a classical sort ;-)
So, starting, for instance, with that list of IPV4 addresses, below :
192.168.5.13 192.168.5.9 192.168.5.205 192.205.54.3 92.168.5.3 9.168.5.3 192.168.210.3 192.168.17.107 192.168.17.3 192.20.2.3 192.205.7.3 12.120.255.0 192.168.210.127 192.168.210.29 1.1.12.127 0.0.0.0 255.255.255.255
Now :
-
Move back to the beginning of your list
-
Open the Replace dialog
-
Enter
(?:^|(?<=\.))\d(\d)?(?=\.|$)
, in the Find what: zone -
Enter
\x20(?1:\x20)$0
, in the Replace with: zone -
Select the Regular expression search mode
-
Click on the Replace All button
You should obtain the modified list, below :
192.168. 5. 13 192.168. 5. 9 192.168. 5.205 192.205. 54. 3 92.168. 5. 3 9.168. 5. 3 192.168.210. 3 192.168. 17.107 192.168. 17. 3 192. 20. 2. 3 192.205. 7. 3 12.120.255. 0 192.168.210.127 192.168.210. 29 1. 1. 12.127 0. 0. 0. 0 255.255.255.255
Notes :
-
This S/R adds :
-
Two space characters to any individual one-digit number of an IPV4 address
-
One space character to any individual two-digits number of an IPV4 address
-
-
The string searched is the part
\d(\d)?
of the search regex, that is to say an individual one or two digits number. Note that in case of a one-digit number, the optional group(\d)
does not exist -
An overall match will occur, ONLY IF it is, both :
-
Preceded by a beginning of line or a
dot
symbol ((?:^|(?<=\.))
) Note the non-capturing group(?:......)
-
Followed by a
dot
symbol or an end of line, ((?=\.|$)
)
-
-
In replacement , we rewrite :
-
A first space character (
\x20
) -
A second space character, only if group 1 does not exist (
(?1:\x20)
, with the ELSE part, after the colon\x20
) -
Finally, the entire searched string (
$0
), which is a one or two digit(s) individual number
Now, sort this list with the option Edit > Line Operations > Sort lines Lexicographically Ascending. You obtain :
0. 0. 0. 0 1. 1. 12.127 9.168. 5. 3 12.120.255. 0 92.168. 5. 3 192. 20. 2. 3 192.168. 5. 9 192.168. 5. 13 192.168. 5.205 192.168. 17. 3 192.168. 17.107 192.168.210. 3 192.168.210. 29 192.168.210.127 192.205. 7. 3 192.205. 54. 3 255.255.255.255
Finally, just get rid of all space characters, with the simple regex S/R, below :
SEARCH
\x20
REPLACE
Leave EMPTY
Et voilà !
Here is your list of IPV4 adresses, sorted in a numeric way :-))
0.0.0.0 1.1.12.127 9.168.5.3 12.120.255.0 92.168.5.3 192.20.2.3 192.168.5.9 192.168.5.13 192.168.5.205 192.168.17.3 192.168.17.107 192.168.210.3 192.168.210.29 192.168.210.127 192.205.7.3 192.205.54.3 255.255.255.255
Best Regards,
guy038
-
-
Great post and detailed explanation.
The following is a solution to a scenario when migrating DHCP reserved MAC addresses. When exporting via command line on server, the subsection regarding reserved IP is not sorted. In efforts to clean-up this list prior to migration, sorting is a great solution.
Exporting DHCP data from Windows Server via command line as explained here:
https://support.microsoft.com/en-us/help/325473/how-to-move-a-dhcp-database-from-a-computer-that-is-running-windows-ntExport:
netsh dhcp server dump > C:\dhcp.txt
Import:
netsh exec c:\dhcp.txt
Within the section “Add ReservedIp to the Scope” each line is dumped like (MacAddress is actual):
Dhcp Server \\domain.name.local Scope 192.168.10.0 Add reservedip 192.168.10.125 MacAddress "DeviceName" "DeviceName" "BOTH"
Leveraging details from a similar post (below) I was able to adapt a version for my specific needs:
https://notepad-plus-plus.org/community/topic/11105/feature-request-sort-by-ip-address-cidr-notationEnter
(?:^|(?<=\.))\d(\d)?(?=\.|$|\s)
, in the Find what: zone
Enter\x23(?1:\x23)$0
, in the Replace with: zoneNote: The addition of the pipe, forward-slash, s (match trailing space)
(I chose ASCII hex 23 (dec 35) number sign as a temporary char place holder not shown in the DHCP server naming. Experiment to see which char works for your needs.)
http://www.ascii.cl/htmlcodes.htmThe end result is proper spacing (padded with “hashes”).
Then sort via “Sort Lines Lexicographically Ascending”
https://superuser.com/questions/762279/sorting-lines-in-notepad-without-the-textfx-plugin#762281Now “unpad” the IP address for later import:
SEARCH\x23
REPLACELeave EMPTY
Hope this helps.
-
Hi,
Thankyou for the post, I have a task for this soon and will give you an update.
Many Thanks
Mark
-
Many Thanks this works great!!!
-
Works great. Tiny addition: make sure you have the correct EOL. I replaced the comma’s in a comma-separated list of IP addresses with ‘\n’ in a CRLF (Windows) document. Sorting as described above produced unpredictable (to me) results.
-
@Matthijs-Wensveen said:
I replaced the comma’s in a comma-separated list of IP addresses with ‘\n’ in a CRLF (Windows) document
For the benefit of any future readers, what you should have done is a regular-expression replacement with
\r\n
for a Windows document.Sorting as described above produced unpredictable (to me) results.
What does this mean? You also said “Works great”–so which is it?
-
Hello is there any option to sort ip addresses if they followed by other data in columns? This method is not working. For example:
IP MAC
192.168.1.140 0023-ac20-3918
192.168.1.49 08ea-2931-ca12
192.168.1.145 08ea-2903-bc32
192.168.1.133 98f1-12ca-2456
192.168.1.73 9440-21ab-2512
192.168.1.134 eceb-565a-2953
192.168.1.132 d067-bc22-3174 -
@michalpl7 said in Can I sort IP addresses in numeric value:
Hello is there any option to sort ip addresses if they followed by other data in columns
@guy038’s regex above assumed the whole line was an IP.
But with a slight tweak – allowing a space or tab to come after the final digits, not just a dot or newline – makes it match any of your examples. So his first FIND WHAT becomes
(?:^|(?<=\.))\d(\d)?(?=\.|\h|$)
His first replacement (with spaces) works… but it’s harder to undo later when you have other spaces in the rest of your line. So I change his first REPLACE WITH to
0(?1:0)$0
so that it inserts one or two zeroes instead of one or two spacesSo
FIND =(?:^|(?<=\.))\d(\d)?(?=\.|\h|$)
REPLACE =0(?1:0)$0
SEARCH MODE = regular expressionThat search/replace will give you
192.168.001.140 0023-ac20-3918 192.168.001.049 08ea-2931-ca12 192.168.001.145 08ea-2903-bc32 192.168.001.133 98f1-12ca-2456 192.168.001.073 9440-21ab-2512 192.168.001.134 eceb-565a-2953 192.168.001.132 d067-bc22-3174
Now you can sort lexicographically ascending as he recommended.
Then you need to change the second search/replace. Instead of searching for spaces and removing them, what we want to do is search for leading zeroes and remove them.
FIND =
\b0+(?=\d+?(?=\.|\h))
REPLACE = leave empty
SEARCH MODE = regular expressionThis got me to
192.168.1.49 08ea-2931-ca12 192.168.1.73 9440-21ab-2512 192.168.1.132 d067-bc22-3174 192.168.1.133 98f1-12ca-2456 192.168.1.134 eceb-565a-2953 192.168.1.140 0023-ac20-3918 192.168.1.145 08ea-2903-bc32
-
@michalpl7 said in Can I sort IP addresses in numeric value:
Hello is there any option to sort ip addresses if they followed by other data in columns? This method is not working. For example:
IP MAC
192.168.1.140 0023-ac20-3918
192.168.1.49 08ea-2931-ca12
192.168.1.145 08ea-2903-bc32
192.168.1.133 98f1-12ca-2456
192.168.1.73 9440-21ab-2512
192.168.1.134 eceb-565a-2953
192.168.1.132 d067-bc22-3174In addition to Peter Jones’ solution, you can also do this in a single operation with the Columns++ plugin.
Select the lines you want to sort.
Select Sort… from the Columns++ menu.
Select:
What to sort: Whole lines
Sort type: Ascending and Numeric
Sort key: Regular expressionCheck: Specify keys using capture groups.
Enter:
Find what:(\d+)\.(\d+)\.(\d+)\.(\d+)
Keys:1,2,3,4
Click OK.
When asked to “Convert to a rectangular selection enclosing the selected lines?” click OK.
-
This post is deleted! -
@PeterJones hello the problem with this command is that it also could change MAC address deletes “00” from it.
-
-
@michalpl7 said in Can I sort IP addresses in numeric value:
it also could change MAC address deletes “00” from it
I was afraid you were going to realize that exception.
(?:^|\.)\K0+(?=\d+?(?=\.|\h))
This changes the requirement to whatever goes before the leading zero digits must be either start of line or a period. But because of the
\K
, you have to use Replace All (it will not work with a single Replace)When I tried my old one on
192.168.001.020 d067-bc22-0004
it wrongly became
192.168.1.20 d067-bc22-4
but when I tried my modified expression, it correctly became
192.168.1.20 d067-bc22-0004
But if @Coises’s plugin solution works for you, I’d go that way, because it doesn’t involve as much complication, and ensures that it’s only dealing with the digits inside an IP address.