Extract 1st three octets of IPV4

Question

Say there is a table that has only one field. The table is called address and has a field called ip that contains the IPV4 address as its value

Data examples

 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 in this table that will return COUNT data in the first three octets

Expected Result

network counting

192.168.120 5

192.168.121 3

I tried using SUBSTR as

 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 that does not have three digits in the 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 an alternative to the above query that will work in all cases above?

Answer 1

Do not perform string operations. you would be better off converting the IP addresses to int and using some bitmask, for example.

 SELECT INET_NTOA(INET_ATON(ipfield) & 0xFFFFFF00) 

Answer 2

You can use substring_index for this:

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

Here's an 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