mysql - Extract 1st Three Octets of an IPV4 -

let's there table having 1 field. table named address , has field named ip contains ipv4 address 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 run query on table return data count on first 3 octets

expected output

network count

192.168.120 5

192.168.121 3

i tried using substr like

select substr(ip,1,10) network,count(*) c address group network having(c>1) 

but problem substr work expected if first 3 octets have 3 digits each, break on ip address not have 3 digits each in first 3 octets. example not work for

192.168.0.0

192.2.3.50

192.23.4.60

question

is there alternate above query work in cases above?

don't string operations. you'd better off converting ips ints , using bitmask, e.g.

select inet_ntoa(inet_aton(ipfield) & 0xffffff00)