Oracle sql find similar names using Soundex

Question

I have table HumanNames

The input is

|HumanName      |

|-------------  |

|Kamil          |

|Azer           |

|John           |

|Elmir          |

|Kamal          |

|Elmar          |

|Orxan          |

|Elnar          |

I need write query which will give result names of like another name.
Desired output is

|NamesWhichLikes  |

|-------------    |

|Kamil,Kamal      |

|Elmir,Elmar,Elnar|

|                 |

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Try this using listagg and group by on soundex of name:

with your_table (HumanName) as (
select 'Kamil' from dual union all 
select 'Azer'  from dual union all 
select 'John'  from dual union all 
select 'Elmir' from dual union all 
select 'Kamal' from dual union all 
select 'Elmar' from dual union all 
select 'Orxan' from dual union all 
select 'Elnar' from dual
)
------ Test data setup ends here ------


select
    listagg(humanname,',') within group (order by humanname) nameswhichlikes
from your_table
group by soundex(humanname)
having count(*) > 1;

Produces:

+---------------------------+
| NAMESWHICHLIKES           |
+---------------------------+
| Elmar,Elmir,Elnar         |
+---------------------------+
| Kamal,Kamil               |
+---------------------------+

In case of duplicate names:

select
    listagg(humanname,',') within group (order by humanname) nameswhichlikes
from (select distinct humanname from your_table)
group by soundex(humanname)
having count(*) > 1;

Oracle sql find similar names using Soundex

Sign up