How to write a join query with like operator

Question

I'm trying to find duplicate ( matched names ) records in a table.

-----------------------------------
|id | name                        |
-----------------------------------
| 1 | Bielefeld Area, Germany     |
-----------------------------------
| 2 | Biella Area, Italy          | 
-----------------------------------
| 3 | Bilbao Area, Italy          | 
-----------------------------------
| 4 | Birmingham, United Kingdom  | 
-----------------------------------
| 5 | Blackburn, United Kingdom   | 
-----------------------------------
| 6 | Blackpool, United Kingdom   | 
-----------------------------------
| 7 | Bogotá D.C. Area, Colombia  |
-----------------------------------

i want to filter records which are repeated with same name like Birmingham United Kingdom , Blackburn United Kingdom , Bilbao Area Italy

result expected

-----------------------------------
|id | name                        |
-----------------------------------
| 2 | Biella Area, Italy          | 
-----------------------------------
| 3 | Bilbao Area, Italy          | 
-----------------------------------
| 4 | Birmingham, United Kingdom  | 
-----------------------------------
| 5 | Blackburn, United Kingdom   | 
-----------------------------------
| 6 | Blackpool, United Kingdom   | 
-----------------------------------

I have tried below query but not working.

select p.* 
from countries p
left join countries u
on concat('|', p.name, '|') like concat('%|', u.name, '|%')

@Nick Yes, but i want to get matched records i.e, United Kingdom matches 3 times and Italy matches 2 time. check expected result table — nagraj, May 08 '19 at 07:18
Your question and expected result don't make sense to me. You seem to be saying give me everything where any distinct node in name appears more than once. How many nodes can there be BTW and are the always separated by comma? — P.Salmon, May 08 '19 at 07:26

score 0 · Answer 1 · answered May 08 '19 at 06:42

0

You can use group by here:

Select count(id) as count, name from countries group by countries.name

answered May 08 '19 at 06:42

Deepak Singh

610
1
7
23

How to write a join query with like operator

1 Answers1