Click here to Skip to main content
11,569,800 members (57,405 online)
Click here to Skip to main content

Tagged as

Find Duplicates with Exact Spell and Verbally Same

, 27 Aug 2010 CPOL 8.6K 5
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!!!

You may also be interested in...

Comments and Discussions

 
GeneralNice My vote is 5 Pin
manoj kumar choubey2-Feb-12 22:35
membermanoj kumar choubey2-Feb-12 22:35 
GeneralReason for my vote of 5 Nice Pin
manoj kumar choubey2-Feb-12 22:30
membermanoj kumar choubey2-Feb-12 22:30 
GeneralThanks for your comment @linuxjr Pin
Hiren Solanki29-Aug-10 20:31
memberHiren Solanki29-Aug-10 20:31 
GeneralReason for my vote of 5 Thanks for sharing this trick Pin
linuxjr29-Aug-10 7:48
memberlinuxjr29-Aug-10 7:48 
GeneralThanks kunal for moving from alternative. i was not able to ... Pin
Hiren Solanki27-Aug-10 3:58
memberHiren Solanki27-Aug-10 3:58 
General[moved from alternative] Sandeep Ramani wrote: Hi Hiren, Go... Pin
KunalChowdhury27-Aug-10 3:30
mvpKunalChowdhury27-Aug-10 3:30 
GeneralThanks for motivating kunal. it took my 1 working day to sol... Pin
Hiren Solanki27-Aug-10 2:29
memberHiren Solanki27-Aug-10 2:29 
GeneralReason for my vote of 5 Nice one Hiren. I liked the way you ... Pin
KunalChowdhury27-Aug-10 2:12
mvpKunalChowdhury27-Aug-10 2:12 
GeneralYa, it is nice when you comparing verbal match. Pin
Hiren Solanki26-Aug-10 19:45
memberHiren Solanki26-Aug-10 19:45 
GeneralReason for my vote of 5 Nice post, Soundex sounds good to wo... Pin
Sandeep Ramani26-Aug-10 19:43
memberSandeep 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 | Terms of Use | Mobile
Web03 | 2.8.150624.2 | Last Updated 28 Aug 2010
Article Copyright 2010 by Hiren solanki
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid