Best way to sort by IP addresses in SQL

Question

I connect to my SpiceWorks database via PHP using the SQLITE3 PDO. I am trying to display a list of devices in order by IP address. Here is my current query:

SELECT name, ip_address FROM `devices` ORDER BY ip_address

The problem with this is that it organizes them weird like this:

enter image description here

Is there any easy way to fix this problem?

It is not possible for me to edit the database because it would through SpiceWorks off. I need a way to do this in SQL.

That depends on how you're storing your IP addresses. Is it a string column? If so, that's your problem. — user229044, Apr 15 '14 at 19:33
Yes. SpiceWorks stores them in a varchar field. There is no way for me to change that because SpiceWorks uses the database too. — AJ Birone, Apr 15 '14 at 19:37
You have to substring that in segments separated by each dot and order as desired.Mysql has substring_index,I`m not familiar with sqlite. — Mihai, Apr 15 '14 at 19:37
Not sure about sqlite, but in mysql you could `ORDER BY INET_ATON(ip_address)` ... don't know if sqlite has a similar function — keithhatfield, Apr 15 '14 at 19:40

score 5 · Answer 1 · answered Feb 24 '16 at 20:04

5

Have you tried INET_ATON function? This is probably a late answer, but maybe it'll help others.

SELECT name, ip_address
FROM devices
ORDER BY
INET_ATON(ip_address)

answered Feb 24 '16 at 20:04

Nekkyo

59
1
1

3

`INET_ATON` is a MySQL function. The question is about SQLITE. – Justin Howard Feb 24 '16 at 21:23

score 3 · Answer 2 · 2016-02-11T09:55:51.170

I have implemented like this:

SELECT IP FROM iplist ORDER BY

CAST(substr(trim(IP),1,instr(trim(IP),'.')-1) AS INTEGER),  

   CAST(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')-1) AS INTEGER), 

        CAST(substr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')-1) AS INTEGER), 

        CAST(substr(trim(IP),length(substr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+ length(substr(trim(IP),1,instr(trim(IP),'.')))+length(substr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)) ,1, instr(substr(trim(IP),length(substr(trim(IP),1,instr(trim(IP),'.')))+1,length(IP)),'.')))+1,length(trim(IP))) AS INTEGER)

DimeCadmium · Answer 3 · 2014-04-15T20:02:59.207

1

ORDER BY
CAST(substr(ip_address,1,instr(ip_address,'.')) AS NUMERIC),
CAST(substr(ip_address,instr(ip_address,'.'), instr(substr(ip_address,instr(ip_address,'.')))) AS NUMERIC),

Something like that should work. It'll be nasty though. (This should sort by the first two octets...)

edited Apr 15 '14 at 20:02

answered Apr 15 '14 at 19:46

DimeCadmium

314
2
9

basically, sort by the string from first letter to the first dot, then from the first dot to the next dot after the first dot... – DimeCadmium Apr 15 '14 at 19:47
That still won't work, it will still sort `113` before `20`. You need to convert each substring to a number for sorting purposes. – user229044 Apr 15 '14 at 19:59
^ Fixed. Added CASTs -- NB I'm not able to test this at the moment; there may be some nice off-by-ones in this. – DimeCadmium Apr 15 '14 at 20:03
Guys read my answer below. It will work. 100% true. In my project it's implemented. – Feb 28 '16 at 04:35

score 0 · Answer 4 · edited Apr 15 '14 at 20:00

0

You have the fields as VARCHAR or some other character field, so it is sorting them by the first number. You need to CAST the type to a number in the order by statement.

Like this:

SELECT name, ip_address
FROM devices
ORDER BY

CAST(PARSENAME([ip_address], 4) AS INT),
CAST(PARSENAME([ip_address], 3) AS INT),
CAST(PARSENAME([ip_address], 2) AS INT),
CAST(PARSENAME([ip_address], 1) AS INT)

Just not sure if this works in SQLlite.....

edited Apr 15 '14 at 20:00

user229044

232,980
40
330
338

answered Apr 15 '14 at 19:40

mw90

132
3
12

no such function: PARSENAME... PARSENAME is not a valid SQLITE function. – AJ Birone Apr 15 '14 at 19:44
I found this: http://community.spiceworks.com/topic/97998-reports-sort-by-ip-address – mw90 Apr 15 '14 at 19:46
This is a valid solution for SQL Server. See: http://www.sql-server-helper.com/tips/sort-ip-address.aspx – kooch Mar 10 '23 at 16:10

score 0 · Answer 5 · answered Jul 12 '19 at 19:07

0

Since I only cared about the last octet, I was able to use the very simple

SELECT name, ip_address FROM `devices`
ORDER BY CAST(substr(ip_address, 10) AS NUMERIC) DESC;

My ip had 9 characters before the last octet

answered Jul 12 '19 at 19:07

Mookie1097

1
1

Best way to sort by IP addresses in SQL

5 Answers5

Linked