How to find similar IPs in MySQL?

I have a table which includes the last IP of the user. Using the following query I am able to find all duplicate IP addresses

SELECT id, ip, COUNT(ip) AS ip_count FROM users GROUP BY ip HAVING ip_count > 1

I am trying to select IPs that are different by the last part only. Here are some examples:

+--------------+---------------+---------+
|     IP 1     |     IP 2      | Similar |
+--------------+---------------+---------+
| 230.15.26.79 | 230.15.26.230 | true    |
| 32.82.0.5    | 32.82.0.180   | true    |
| 230.15.26.79 | 193.230.15.26 | false   |
| 230.15.26.79 | 230.15.39.115 | false   |
+--------------+---------------+---------+

I could manually find if there are similar IPs to one in particular using the following command:

SELECT id, ip FROM users where ip LIKE "230.15.26.%"

However, this would mean that I have to loop the entire database, which is quite sizably voluminous.

Is there another way that I can use to do the described above with one to two queries only?

Solution

You can extract the required data with a query similar to:

SELECT SUBSTRING_INDEX( ip, '.', 3), COUNT(*)  
FROM ipadd
GROUP BY SUBSTRING_INDEX( ip, '.', 3)
HAVING COUNT(*) > 1

assuming a table structure in the lines of

create table ipadd(id INT, ip VARCHAR(15));

You can see it in action here