MYSQL finding common values on name column

Question

I have a mysql table with a field name which might have common entries like below

name
---
abc
abc.com

I want to select all common values and i have written the query below:

select * from table
group by name having count(*) >= 2;

Here, name is basically server hostname. So, as per definition, I need to compare only the first value before . So, server is same as server.abc.com I am getting 0 rows selected which doesn't look correct

You need to elaborate on your definition of "duplicate" because `abc` and `abc.com` are not normally considered the same values — Paul Maxwell, Dec 22 '17 at 05:00

score 0 · Answer 1 · answered Dec 22 '17 at 06:21

To your RDMS, these are not duplicate values. The database is comparing matching strings, and 'abc' != 'abc.com'. What I assume you want is to check for multiple urls, regardless of the top level domain (.com, .org, etc). To do this, we can grab the URL before the final period.

select 
  substr(
    name
    , 0
    , locate('.', reverse(name))
  )
  , count(*) 
from 
  table
group by 
  substr(
    name
    , 0
    , locate('.', reverse(name))
  ) 
having count(*) >= 2;

The code above will disregard the domain, but you may still have issues with subdomains (news.abc.com, videos.abc.com, etc) or protocal indicators (http, https, etc). I will leave those as an exercise for you should you need them.

score 0 · Answer 2 · answered Dec 22 '17 at 06:44

As far as i understand your question i created a query may be it will help you.
since you did not shown proper database structure, i created same scenario in a table and created query as per that.
Eg:

select count(c1.city_name) as totals from city c1 inner join city c2 on c1.city_name LIKE CONCAT('%', c2.city_name, '%') group by c1.city_id having totals >1;

score 0 · Answer 3 · answered Dec 22 '17 at 07:57

0

try something like this, this work for oracle

select * from(
SELECT a.name, count(b.id) duplicates  FROM  table a,table b 
WHERE a.name LIKE CONCAT(b.name, '%')
group by a.name
) where duplicates >=2

answered Dec 22 '17 at 07:57

janith1024

1,042
3
12
25

GeorgeS · Answer 4 · 2017-12-22T07:34:11.133

-1

select name, count(name) 
  from (select substring(name, 1, 
               if(position("." in name)=0,
                 length(name),
                 position("." in name)-1) 
               ) name
         from table) t1
  group by name 
  having count(name) >= 2;

if you want all entries, remove "having count(name) >= 2"

edited Dec 22 '17 at 07:34

answered Dec 22 '17 at 05:11

GeorgeS

99
7

MYSQL finding common values on name column

4 Answers4