Retrieve octets from IP addresses

from the Artful Common Queries page


If ip is for the form N.N.N.N where N may be 1, 2 or 3 digits, how to group and count by just the first three octets, ie the ip class?

SELECT 
  LEFT(ip, CHAR_LENGTH(ip) - LOCATE('.', REVERSE(ip))) as ipclass,
  COUNT(*)
FROM tbl
GROUP BY ipclass; 
Hamilton Turner notes we can find the first octet with LEFT(ip,LOCATE('.',ip)-1).

Last updated 15 Jan 2020




Return to the Artful Common Queries page