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 column data

In a table I have users registering for a mailing. The mailing should be
limited to one per household, so I would like to exclude all but 1 from each
unique home address. There is a unique id for each registrant, but its
possible another family member could have registered more than once with the
same home address. Whats the best way to do this?
TIA
VanIt depends on how the family members are represented in your schema. Is
there some kind of relationship which identifies one registrant as a family
member of another? Is so, to which registrant should the mailing be
addressed?
Please post your table schema, a few sample data & expected results so that
others can better understand your requirements.
Based on similar requests posted in this forum, the query most likely look
something along the lines of:
SELECT *
FROM tbl t1
WHERE < registrant Identifier > =
( SELECT MAX( < registrant Identifier > )
FROM tbl t2
WHERE t2. < address Identifier > = t1. < address Identifier > )
where < registrant Identifier > is the column or set of columns which
uniquely identifies a registrant while < address Identifier > is column or
set of columns which uniquely identifies an address.
Anith|||Thanks for your reply. Unfortunately I got an out of memory exception when I
tried that query.
As you suggested to post my current query looks like this:
SELECT FirstName, LastName, Email, Address1, Address2, City, State, Zip
FROM Orange_Members
INNER JOIN Orange_MembersPromo ON Orange_Members.RegId =
Orange_MembersPromo.RegId
WHERE(Orange_MembersPromo.PromoId = 600)
In the Orange_Members table there may be Address1 values that are duplicate.
There is a unique id primary key in Orange_Members called RegID.
I would like to return every unique Address1 row. In otherwords if Address1
has:
120 Greene Street twice, I would only like to have the first one that
occurs(actually I really dont care which row it is, as long as it only comes
up once) -- and of course any Address that is only in one row in the DB
should be returned by the select also.
TIA

> Based on similar requests posted in this forum, the query most likely look
> something along the lines of:
> SELECT *
> FROM tbl t1
> WHERE < registrant Identifier > =
> ( SELECT MAX( < registrant Identifier > )
> FROM tbl t2
> WHERE t2. < address Identifier > = t1. < address Identifier > )
> where < registrant Identifier > is the column or set of columns which
> uniquely identifies a registrant while < address Identifier > is column or
> set of columns which uniquely identifies an address.
> --
> Anith|||>> As you suggested to post my current query ..
Actually I asked you to post your table schema, a few sample data & expected
results. For details refer to www.aspfaq.com/5006
Based on your narrative, here is another guesswork:
SELECT *
FROM Orange_Members m1
WHERE m1.RegId = ( SELECT MAX( m2.RegId )
FROM Orange_Members m2
WHERE m2.Address1 = m1.Address1 )
AND m1.PromoId = 600 ;
Anith|||Thanks, that worked!