How can I query rows for similar /duplicate names

[hu]

Hi<br>

I am trying to run a query that will return rows which are actually duplicate but entered in the database in a different format.<br>

For eg:<br>

David Whitter R<br>

David R Whitter<br>

I used the soundex function , but did not return rows as expected.<br>

It returned only duplicate rows.<br>

The code i used is below<br>

select p.full_name,count(*)<br>

from person p<br>

where soundex(full_name)in (select soundex(p1.full_name) from person p1)<br>

group by p.full_name<br>

having count(*) >1 <br>

order by 1<br>

Any help is appreciated<br>

Thanks<br>

 

[Snaggs]

How about using the same idea but joining the table to itself on the primary key and then getting a list of all names that sound alike but are actually different. For example, if the field was FULL_NAME and the Key was EmployeeID you would join the table to itself on EmployeeID, then use your Soundex function on the full_name from the first instance of the table and say 'WHERE P1.FullName &lt;&gt; P2.FullName'. Of course you have to alias the tables to do this, but it looks like you already know how to do that. Use an Inner Join for your join type. <p>Steve Meier<br><a href=mailto:sdmeier@jcn1.com>sdmeier@jcn1.com</a><br><a href= > </a><br>

 

[hu]

Hi Steve<br>
Thanks for your idea.Taking your advice<br>
I have re-written my query like below.Even though i am getting duplicates and similiar sounding names, i am still missing names that i know are written in similiar fashion but are in fact duplicate.<br>
I also forgot to mention that the Full_name field is also split into First_name,Last_name and Middle_name fields in the same table. <br>
<br>
select p.full_name,p.person_id,count(*)<br>
from person p,person p1<br>
where p.person_id=p1.person_id<br>
and (soundex(p.first_name)=soundex(p1.first_name)<br>
and p.last_name=p1.last_name)<br>
or(soundex(p.last_name)=soundex(p1.last_name)<br>
and p.first_name=p1.first_name)<br>
group by p.full_name,p.person_id<br>
having count(*) &gt;1<br>
order by 1<br>
<br>
If you think that i should write this another way<br>
please suggest.<br>
Thanks a bunch

 

[calahans]

Hi,<br>
<br>
Try your query without the soundex on the laft hand side for example:<br>
<br>
select p.full_name,p.person_id,count(*)<br>
from person p,person p1<br>
where p.person_id=p1.person_id<br>
and (p.first_name=soundex(p1.first_name)<br>
and p.last_name=p1.last_name)<br>
or(p.last_name=soundex(p1.last_name)<br>
and p.first_name=p1.first_name)<br>
group by p.full_name,p.person_id<br>
having count(*) &gt;1<br>
order by 1 <br>
<br>
Cal

 

[bigscouse]

Would this not work?<br>
select p.full_name,p.person_id<br>
from person p,person p1<br>
where p.person_id=p1.person_id<br>
and (p.first_name = p1.first_name <br>
and p.last_name != p1.last_name )<br>
or (p.last_name = p1.last_name<br>
and p.first_name != p1.first_name)<br>
order by 1 <br>
<br>
<p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>

 

[Nicholle]

Hi Hu

Did the SQL query posted March 08, 2000 work for you.

I am seeking assistance in incorporating Soundex within my query. Should I perform the query and then if no records are retrieved then perform the Soundex to gather records which may be similar.

Any guidance would be appreciated. I am in uncharted waters

Nicholle