Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Find Duplicates with Exact Spell and Verbally Same

, 27 Aug 2010
Rate this:
Please Sign up or sign in to vote.
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 Name Last Name
Hiren Solanki
Hiren Solanki
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 Name Last Name
Hiren Solanki
Hyren Solanki
above are the duplicates.
 
So Lets Create one Temp. Table
using Following Syntax
 
1. Create Temperory Table
 
create table #tempProfile
(
ID int,
FirstName varchar(max),
LastName varchar(max),
Designation varchar(max)
)
 
2. Fill The Data Into Table
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 )
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)
 
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.
 
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)

Share

About the Author

Hiren solanki
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 Pinmembermanoj kumar choubey2-Feb-12 22:35 
GeneralReason for my vote of 5 Nice Pinmembermanoj kumar choubey2-Feb-12 22:30 
GeneralThanks for your comment @linuxjr PinmemberHiren Solanki29-Aug-10 20:31 
GeneralReason for my vote of 5 Thanks for sharing this trick Pinmemberlinuxjr29-Aug-10 7:48 
GeneralThanks kunal for moving from alternative. i was not able to ... PinmemberHiren Solanki27-Aug-10 3:58 
General[moved from alternative] Sandeep Ramani wrote: Hi Hiren, Go... PinmvpKunalChowdhury27-Aug-10 3:30 
GeneralThanks for motivating kunal. it took my 1 working day to sol... PinmemberHiren Solanki27-Aug-10 2:29 
GeneralReason for my vote of 5 Nice one Hiren. I liked the way you ... PinmvpKunalChowdhury27-Aug-10 2:12 
GeneralYa, it is nice when you comparing verbal match. PinmemberHiren Solanki26-Aug-10 19:45 
GeneralReason for my vote of 5 Nice post, Soundex sounds good to wo... PinmemberSandeep Ramani26-Aug-10 19:43 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 28 Aug 2010
Article Copyright 2010 by Hiren solanki
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid