Duplicate Removal and Intelligent T-SQL

I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db. Finding and removing the
existing dups is not rocket science, but I am looking for a complete
solution.

We also want to be able to come up with a T-SQL script to be
intelligent enough to know if:

"Bill Jones" is the same as "Billy Jones" or

"Bill Jones" is the same as "William Jones" or

"Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
St."

Purchasing scripts from a company is not out of the question. Its a
matter of script cost versus my hourly rate to create them... Needless
to say, something cheap.

We have only heard of one app that is dynamic enough to handle this,
and the cost was way, way out of our range, 6 figures is almost 2
years salary.

Any ideas would be greatly appreciated.funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> I am looking for some T_SQL code that would help us eliminate
> duplicate data entry into our mailing db. Finding and removing the
> existing dups is not rocket science, but I am looking for a complete
> solution.
> We also want to be able to come up with a T-SQL script to be
> intelligent enough to know if:
> "Bill Jones" is the same as "Billy Jones" or
> "Bill Jones" is the same as "William Jones" or
> "Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
> St."
> Purchasing scripts from a company is not out of the question. Its a
> matter of script cost versus my hourly rate to create them... Needless
> to say, something cheap.
> We have only heard of one app that is dynamic enough to handle this,
> and the cost was way, way out of our range, 6 figures is almost 2
> years salary.
> Any ideas would be greatly appreciated.

This may help - I've used it successfully to do fuzzy matching of
duplicate company names:

http://www.winnetmag.com/SQLServer/...6094/26094.html

Simon|||Simon,

That was a very interesting article. It also came with about 1200
lines of t-SQL. I dont think thats exactly what I need, but I can
definetely use pieces of it.

Thanks

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0406072358.1cb5c387@.posting.google.com>...
> funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> > I am looking for some T_SQL code that would help us eliminate
> > duplicate data entry into our mailing db. Finding and removing the
> > existing dups is not rocket science, but I am looking for a complete
> > solution.
> > We also want to be able to come up with a T-SQL script to be
> > intelligent enough to know if:
> > "Bill Jones" is the same as "Billy Jones" or
> > "Bill Jones" is the same as "William Jones" or
> > "Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
> > St."
> > Purchasing scripts from a company is not out of the question. Its a
> > matter of script cost versus my hourly rate to create them... Needless
> > to say, something cheap.
> > We have only heard of one app that is dynamic enough to handle this,
> > and the cost was way, way out of our range, 6 figures is almost 2
> > years salary.
> > Any ideas would be greatly appreciated.
> This may help - I've used it successfully to do fuzzy matching of
> duplicate company names:
> http://www.winnetmag.com/SQLServer/...6094/26094.html
> Simon|||>> I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db .. not rocket science, but I
am looking for a complete solution. <<

Yes, it is :) Get a package and save yourself 5-10 man-years.
Call the NCSC at 800-238-3150 and request the following free
publications:

PUB 25 - Designing Business Letter Mail
PUB 28 - Postal Addressing Standards
PUB 221 - Addressing for Success

Buy a mailing list product to scrub your data. It is much harder than
it looks. Here is an old list of companies that sell ZIP code and
mailing related product:

BCC Software Inc.
39 Saginaw Drive #630
Rochester, NY 14623

Comco, Inc.
2211 Grant Street
Box #349
Bettendorf, IA 52722

CD Light
8861 South Silverstone Way
Sandy, UT 84093
CD-Rom of ZIP+4 for $30

Dassgar Systems
Box #915
Bennington, VT 05201-0915
Global-Z; international postal codes for lots of places

Eric Isaacson Software
416 East Bloomington
Bloomington, IN 47401-4739
demo version of ZipKey

Group 1 Software
4200 Parliament Place #600
Lanham, MD 20706

Melissa Data Corporation
32122 Paseo Adelanto
San Juan Capistrano, CA 92675

Search Software America
1445 East Putnam Ave
Old Greenwich, CT 06870

(ask for their booklet "The Math, Myth & Magic of Name Searching and
Matching". It is quite informative)

For 'UK postcode databases', the Royal Mail holds the copyright and
will licence you an 'outwards' database (i.e. the first segment of the
full postcode) for about 150 a year, or an 'inwards' database (i.e.
the full 9- or 10-character postcode) for about 500 UK pounds per
year.

KJL Software (www.kjlsoftware.com) gives you 5-digit ZIP Code, City,
State,USPS Status Code, LATEST Area Code(s) from NANPA, Time
Zone,Latitude, and Longitude for all valid US Postal Service 5 digit
ZIP Codes/City/State combinations and a Distance Calculator.|||I'd also recommend http://www.qas.com/

We use them for address checking/changing and there is additional
functionality (in the UK at least) to check against the electoral role
to verify if someone is at a specific address.

Failing that, UK wise, Experian or Equifax may be of some help. I'm
not sure what they do outside the UK though.

Hope that helps.

Duplicate record removal

I can identify the duplicate rows and show number of duplication by using
this
SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
FROM USERS
GROUP BY [USER_NUMBER], [EMAIL]
HAVING Count([USER_NUMBER]) > 1
I can't figure out how to delete duplicate records and keep one distinct
record with smallest ID
Example:
TABLE: USERS
ID USER_NUMBER EMAIL
1 12 a@.a.com
2 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
5 14 c@.c.com
Output:
ID USER_NUMBER EMAIL
1 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
Thanks,
HowardDELETE FROM users
WHERE EXISTS
(SELECT *
FROM users AS U
WHERE U.user_number = users.user_number
AND U.email = users.email
AND U.id < users.id) ;
Nulls, if any, will be ignored.
David Portas
SQL Server MVP
--|||Hi Howard,
Try this statement
Delete From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
PLEASE NOTE : I have not tested this statement so please run the following
query and ensure that this is what you want to be deleted.
Select * From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
Do mail if this post help s you.
Vishal Khajuria
9886170165
IBM Bangalore
"Howard" wrote:

> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>|||thanks for your replies
I solved the problem.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I can identify the duplicate rows and show number of duplication by using
>this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||Hi,
This will solve your query ...
delete from test where test.id not in (select top 1 b.id from test b
where (test.user_number=b.user_number) and
(test.email=b.email))
Regards,
Predrag Stojanovic
Analist/Programer
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||You missed out the ORDER BY. TOP 1 may not retrieve the minimum value
of ID unless you use ORDER BY.
Also, notice that this version will not delete any rows for a
particular user_name if a NULL ID exists for that user_name. Probably
that's not likely - I'd guess that ID is the PRIMARY KEY - but that's
one potential catch with TOP and with NOT IN.
David Portas
SQL Server MVP
--|||Here is the process:
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Substitute your own names for those within the [].
Unique Column Names are those columns that uniquely identify each row.
Column Names is the full list of columns in your table.
HTH,
Mike
"Howard" wrote:

> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>sql