How to remove or delete a duplicated phone numbers

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

General SQL Server Forums

New to SQL Server Programming

How to remove or delete a duplicated phone numbers

Author

Topic

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-19 : 12:51:25

We have a database with imported customer phone numbers from an application. THis application didn't have intelligent feature to filter the duplicated phone numbers.

1)How to remove the duplicated records in SQL DB.
2)Does this need to create a stored procedure? It needs to be raun daily.

Someone refer MS article to delete duplicated records but it didn't work:
http://support.microsoft.com/kb/70956/en-us

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 12:55:50

do like


DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PhoneNumber ORDER BY NEWID()) AS Rn
FROM Table
)t
WHERE Rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-19 : 13:19:11

There are mutiple duplicated phone numbers, there are different phone numbers and not all the same ones.

quote:
Originally posted by visakh16

do like

DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PhoneNumber ORDER BY NEWID()) AS Rn
FROM Table
)t
WHERE Rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 13:26:30

what they're different? then what according to you are duplicates? post some sample data to show your scenario at least rather than keeping us guessing!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-19 : 13:57:53

There are a lot of member phone numbers in the records, some people have a same phone numbers to associate with auto-loan, real estate loan and credit card loan). We try to delete the duplicated phone numbers assciated with one person in the SQL DB .

I hope I make it clear to you.

THis is a collection software for some bank to call people who get delinqent loans.

quote:
Originally posted by visakh16

what they're different? then what according to you are duplicates? post some sample data to show your scenario at least rather than keeping us guessing!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-19 : 14:07:47

[quote]Originally posted by Ginger8990

There are a lot of member phone numbers in the records, some people have a same phone numbers to associate with auto-loan, real estate loan and credit card loan). We try to delete the duplicated phone numbers assciated with one person in the SQL DB .

I hope I make it clear to you.

THis is a collection software for some bank to call people who get delinqent loans.

This is the some examples:
2000497 129274 302724395 9254587899 Bowling, DAVID A 1725 BOLTZEN ST BRENTWOOD CA 94513 45 189295 553.99 00:00.0 22 53928.37 1.63246E+16 NULL E 1 NULL NULL NULL NULL NULL NULL 1 0 NULL
2005497 129274 302724395 9254587899 Bowling, DAVID A 1725 BOLTZEN ST BRENTWOOD CA 94513 0 238128 20.93 00:00.0 79 -20.93 1.63246E+16 NULL E 1 NULL NULL NULL NULL NULL NULL 1 0 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:10:17

I repeat
post some sample data to show your scenario at least rather than keeping us guessing!

see below on how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-19 : 14:13:59

the sample is aboved:

2000497 129274 302724395 9254587899 Bowling, DAVID A 1725 BOLTZEN ST BRENTWOOD CA 94513 45 189295 553.99 00:00.0 22 53928.37 1.63246E+16 NULL E 1 NULL NULL NULL NULL NULL NULL 1 0 NULL
2005497 129274 302724395 9254587899 Bowling, DAVID A 1725 BOLTZEN ST BRENTWOOD CA 94513 0 238128 20.93 00:00.0 79 -20.93 1.63246E+16 NULL E 1 NULL NULL NULL NULL NULL NULL 1 0 NULL

--------------------------------------------------------------------------------

quote:
Originally posted by visakh16

I repeat
post some sample data to show your scenario at least rather than keeping us guessing!

see below on how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-09-19 : 17:21:42

Holy crap - is this real data?! David Bowling should be pissed....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 02:36:50

of these records, what should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-20 : 14:28:49

The data is altered, not real name and numbers:)

Nobody is stupid to do such foolish thing providing all real information:)

quote:
Originally posted by Aleph_0

Holy crap - is this real data?! David Bowling should be pissed....

Ginger8990
Starting Member

28 Posts

Posted - 2011-09-20 : 14:31:02

I am not SQL DBA so I only see the info by clicking Edit All 200 Rows.

I was asked to explore it to see whetherthe duplicated phone numbers can be removed by a stored procedures or script. But there are 100 people names so so many duplicated phone numbers for each person.

quote:
Originally posted by visakh16

of these records, what should be your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 22:02:59

so you want one phone number per person?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources