sqlite

Order IP addresses in SQLITE3

I would like to ask you how to order IP in ascendant way in SQLITE. I tried store them like TEXT and INTEGER, but it doesn't work.

My query is:

(SELECT * FROM tbl_device ORDER BY ip ASC")

My output is:

192.168.1.1
192.168.1.10
192.168.1.11
192.168.1.111
192.168.1.12
192.168.1.120

Is there way to ORDER them like below:

192.168.1.1
192.168.1.10
192.168.1.11
192.168.1.12
192.168.1.111
192.168.1.120

Thank you very much in advance.

Solution

  • The easiest way to do this would be to store the IP addresses in a format that is sortable, i.e., as four numbers, or as a single 32-bit number. If you don't want to format the addresses every time they are displayed, use a separate column.