Select similar IP addresses - ignore last 3 digits

Question

I have a table with usernames and IPs.
I need to get a list of the users who have similar IP -ignore last 3 or 2 or 1 digit(s)-

Example:
Count 190.200.210.180 and 190.200.210.60 as the same IP.
Count 205.50.4.30 and 205.50.4.197 as the same IP.

The field type of IPs is set as varchar and this is something I cannot change at the moment.

Currently I'm using :

SELECT GROUP_CONCAT( username ) names, IPs, COUNT( IPs ) AS Instances 
FROM users
GROUP BY IPs
HAVING (COUNT(IPs) >1)
ORDER BY  `Instances ` DESC 

to get the users with the same IP.

Is it possible with the field of the IPs being set as varchar to make a select statement to group users with similar ips?

Thank you in advance for your help.

Answer 1

Try this:

SELECT 
    GROUP_CONCAT(username) AS names, 
    SUBSTRING_INDEX(IPs, '.', 3) AS IPs
    COUNT(*) AS Instances 
FROM
    users
GROUP BY 
    SUBSTRING_INDEX(IPs, '.', 3)
HAVING
    COUNT(*) > 1
ORDER BY
    Instances DESC

Answer 2

The function SUBSTR_INDEX makes parsing an IP address an easy operation:

  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(y.IPs,'.',1),'.',-1) a,
         SUBSTRING_INDEX(SUBSTRING_INDEX(y.IPs,'.',2),'.',-1) b,
         SUBSTRING_INDEX(SUBSTRING_INDEX(y.IPs,'.',3),'.',-1) c,
         SUBSTRING_INDEX(SUBSTRING_INDEX(y.IPs,'.',4),'.',-1) d
  FROM (SELECT ... FROM users) y

Using the aliased octets a,b,c,d as a base, you should be able to apply similar logic to your question and perform the octet-matching you desire...