Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL
Tip/Trick

Find Duplicates with Exact Spell and Verbally Same

Rate me:
Please Sign up or sign in to vote.
4.88/5 (5 votes)
27 Aug 2010CPOL 22.8K   5   10
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)


Written By
Software Developer
India India
He is a Smart IT devloper with Few years of Expeariance But having Great command on ASP.net,C#,SQL Query,SSRS,Crystal Reports

Apart from that He Loves multimedia work too, Master of Adobe photoshop, Illustrator, CSS , HTML and all things.

He is Currently working in Microsoft Dynamics CRM and Having Nice Expearince with CRM. CRM Rocks!!!

Comments and Discussions

 
GeneralNice My vote is 5 Pin
Manoj Kumar Choubey2-Feb-12 22:35
professionalManoj Kumar Choubey2-Feb-12 22:35 
GeneralReason for my vote of 5 Nice Pin
Manoj Kumar Choubey2-Feb-12 22:30
professionalManoj Kumar Choubey2-Feb-12 22:30 
GeneralThanks for your comment @linuxjr Pin
Hiren solanki29-Aug-10 20:31
Hiren solanki29-Aug-10 20:31 
GeneralReason for my vote of 5 Thanks for sharing this trick Pin
linuxjr29-Aug-10 7:48
professionallinuxjr29-Aug-10 7:48 
GeneralThanks kunal for moving from alternative. i was not able to ... Pin
Hiren solanki27-Aug-10 3:58
Hiren solanki27-Aug-10 3:58 
General[moved from alternative] Sandeep Ramani wrote: Hi Hiren, Go... Pin
Kunal Chowdhury «IN»27-Aug-10 3:30
professionalKunal Chowdhury «IN»27-Aug-10 3:30 
GeneralThanks for motivating kunal. it took my 1 working day to sol... Pin
Hiren solanki27-Aug-10 2:29
Hiren solanki27-Aug-10 2:29 
GeneralReason for my vote of 5 Nice one Hiren. I liked the way you ... Pin
Kunal Chowdhury «IN»27-Aug-10 2:12
professionalKunal Chowdhury «IN»27-Aug-10 2:12 
GeneralYa, it is nice when you comparing verbal match. Pin
Hiren solanki26-Aug-10 19:45
Hiren solanki26-Aug-10 19:45 
GeneralReason for my vote of 5 Nice post, Soundex sounds good to wo... Pin
Sandeepkumar Ramani26-Aug-10 19:43
Sandeepkumar Ramani26-Aug-10 19:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.