Extract 1st Three Octets of an IPV4

Question

Let's say there is a table having only one field. Table is named address and has a field named ip which contains an IPV4 address as its value

Sample Data

192.168.120.201
192.168.120.202
192.168.120.203
192.168.120.204
192.168.120.205
192.168.121.3
192.168.121.50

I need to run a query on this table which will return data COUNT on The First Three Octets

Expected Output

network count

192.168.120 5

192.168.121 3

I tried using SUBSTR like

SELECT SUBSTR(ip,1,10) as network,COUNT(*) as c FROM address GROUP BY network HAVING(c>1)

But the problem is that this SUBSTR will only work as expected if all the first 3 Octets have 3 digits each, but this will break on any ip address which does not have 3 digits each in first three octets. For example this will not work for

192.168.0.0

192.2.3.50

192.23.4.60

Question

Is there any alternate to the above query which will work in all the cases above?

Answer 1

Don't do string operations. you'd be better off converting the IPs to ints and using some bitmask, e.g.

SELECT INET_NTOA(INET_ATON(ipfield) & 0xFFFFFF00)

Answer 2

You could use substring_index to do this:

SELECT substring_index(network, '.', 3) AS Octet,
       COUNT(*)
  FROM address
  GROUP BY Octet

Here's a SQLFiddle example

Answer 3

I would suggest using SUBSTRING_INDEX for this:

SELECT SUBSTRING_INDEX(ip, '.', 3) as network, COUNT(*) as c
FROM address
GROUP BY network
HAVING(c>1)
LIMIT 500