Kamil Ibadov Kamil Ibadov - 4 years ago 146
SQL Question

Oracle sql find similar names using Soundex

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|
| |

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download