Finding near duplicate records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Finding near duplicate records

Author

Topic

ScotKinney
Starting Member

2 Posts

Posted - 2010-11-02 : 14:37:45

I have a field for citizen_name where we used to put quotes around a users name if they didn't have a citizen_number as they signed up. Now we've disabled that in the asp page and no longer need quotes around users names for anything. So, now I'm trying to remove the quotes from all names in the citizen_name field. But, since we disaled that feature on the asp page, new users have come along and created new accounts with the same name as older users who had quotes. So I have a name like NewUser and a name like "NewUser". When I try to remove the quotes from all entries in the citizen_name field I get the following error.

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.citizen_one' with unique index 'ix_citizen_one_citizen_name'.
The statement has been terminated.

I need to be able to find all of the names that are dupes with quotes and no quotes to remove or alter them so they aren't identical after removing the quotes. Then I can run the command to remove all quotes from all names.

I know almost nothing about MS SQL 2008 or any other DB so please be specific if possible!

The table in question is called citizen_one the field is citizen_name

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-02 : 14:51:59

well just to find the "dupes" you can use something like this:


select replace(citizen_name, '"','')	as [Replaced]
	,min(citizen_name)			as [minName]
	,max(citizen_name)			as [maxName]
	,count(*)				as [dupeCount]
from	citizen_one 
group by replace(citizen_name, '"','')
having count(*) > 1

Just out of curiosity, what happens if two different citizens have the same name? There have to be a bunch of people named John Smith and Mohamed Ali out there.

Be One with the Optimizer
TG

ScotKinney
Starting Member

2 Posts

Posted - 2010-11-02 : 16:09:01

@TG...thanks, I'll try this. The website doesn't allow duplicate names, but since we removed the code that added quotes to some names, it's opened up the possibility for the dupes. At least that's what I 'think' is going on based on that error. This is a Db for a game.

I ran the code you suggested and got what looks like 12 dupes but I'm not sure what to do with them now. I guess make note and find each one and fix them maybe by altering them slightly...a dozen out of 50,000 I can afford to lose.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources