Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

Find Duplicates with Exact Spell and Verbally Same

4.88/5 (5 votes)
27 Aug 2010CPOL 25.7K  
Retrieve full duplicate rows with some of column value having duplication
Hi All,
Lets Get into Scenario,
1. Find all the duplicate records in a table where firstname and lastname of the one record is same with firstname and lastname of other record. E.g

First NameLast Name
HirenSolanki
HirenSolanki

above are the duplicates


2. Find all the duplicate records in a table where firstname and lastname of the one record is verbally same with firstname and lastname of other record E.g

First NameLast Name
HirenSolanki
HyrenSolanki

above are the duplicates.

So Lets Create one Temp. Table
using Following Syntax

1. Create Temperory Table

SQL
create table #tempProfile
(
ID int,
FirstName varchar(max),
LastName varchar(max),
Designation varchar(max)
)


2. Fill The Data Into Table
SQL
insert into #tempProfile
select 1, 'Hiren','Solanki','Devloper'
union all
select 2,'Hyren','Solanki','Devloper'
union all
select 3,'Virang','Patel','Sr.Devloper'
union all
select 4 ,'Rajesh','Thakur','Tech. Lead'
union all
select 5 ,'Sandeep','Ramani','Devloper'
union all
select 6 ,'Sandip','Ramani','Devloper'
union all
select 7 ,'Bharat','Arora','Project Manager'
union all
select 8 ,'Hiren','Solanki','CRM consultant'
union all
select 9,'Rajesh','Thakur','CRM Consultant'



Now The Query to Retrive to First Scenario. ( Exact Match )
SQL
WITH Temp_CTE(FirstName,LastName,DupCount)
AS
(
SELECT FirstName,LastName,COUNT(*) DupCount
FROM #tempProfile
GROUP BY FirstName,LastName
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1
FROM Temp_CTE
WHERE FirstName = TP.FirstName AND LastName = TP.LastName
)


and Now for the 2nd Scenarion (Verbal Match)

SQL
WITH Temp_CTE(FirstName,LastName,DupCount)AS
(
SELECT SOUNDEX(FirstName),SOUNDEX(LastName),COUNT(*) DupCount
FROM #tempProfile
GROUP BY SOUNDEX(FirstName),SOUNDEX(LastName)
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1 FROM Temp_CTE
WHERE FirstName = SOUNDEX(TP.FirstName)
AND LastName = SOUNDEX(TP.LastName)
)


Done.

below is the full code if you Wanna have a Test.

SQL
create table #tempProfile(ID int,FirstName varchar(max),LastName varchar(max),Designation varchar(max))
GO
insert into #tempProfile
Select 1, 'Hiren','Solanki','Devloper'
union all
select 2,'Hyren','Solanki','Devloper'
union all
select 3,'Virang','Patel','Sr.Devloper'
union all
select 4 ,'Rajesh','Thakur','Tech. Lead'
union all
select 5 ,'Sandeep','Ramani','Devloper'
union all
select 6 ,'Sandip','Ramani','Devloper'
union all
select 7 ,'Bharat','Arora','Project Manager'
union all
select 8 ,'Hiren','Solanki','CRM consultant'
union all
select 9,'Rajesh','Thakur','CRM Consultant'
GO
WITH Temp_CTE(FirstName,LastName,DupCount)
AS
(
SELECT FirstName,LastName,COUNT(*) DupCount
FROM #tempProfile
GROUP BY FirstName,LastName
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1
FROM Temp_CTE
WHERE FirstName = TP.FirstName AND LastName = TP.LastName
)
GO

WITH Temp_CTE(FirstName,LastName,DupCount)AS
(
SELECT SOUNDEX(FirstName),SOUNDEX(LastName),COUNT(*) DupCount
FROM #tempProfile
GROUP BY SOUNDEX(FirstName),SOUNDEX(LastName)
HAVING COUNT(*) > 1
)
SELECT * FROM #tempProfile TP
WHERE
EXISTS
(
SELECT 1 FROM Temp_CTE
WHERE FirstName = SOUNDEX(TP.FirstName)
AND LastName = SOUNDEX(TP.LastName)
)
drop table #tempProfile

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)