I have a client that wants to take demographic data (lastname, address, zip) from their billing program and compare it against a bulk mailing data file to eliminate sending advertising materials promoting their business to address that are already their clients.

I have written code to extract from the billing system the lastname, street address (numbers and name) and zip code and store it in one database file. I then took the CSV file from the bulk mailer and stored the lastname, street and address from here into its own database file.

Now I need to compare one against the other and eliminate from the bulk mail file, those records that match so they don't get a new mailing. Because the data was typed in a variety of formats, spellings, abbreviations, etc... the possibilities of an exact match are going to be really slim.

My original intention was to compare the bulkmail data (BM) with each record in the application data (AP). I was going to take each field seperately and remove letters/number from each that matched using the STRTRAN function. What I would have left from each AP record would be the remaining number of letters/numbers that could not be removed. If I had 100% removed it was an exact match... if I have 90% removed it was a probably match... if I had 80% removed it was a possible match. Possible matches would have been put in a table for operator intervention to tell if it was a match or not.

It seemed to work... except... it is slow. Well not really that slow, when you consider what it is doing, but slow all the same. Basically, it had to compare each of the 47,000 BM records against 8,700 AP records. It is taking on a Pentium II 266Mhz with 128MB almost 2 minutes per BM record or basically 26 hours to run the comparison.

Sooooo....

This is going to fly with the client. Anyone have any better ideas to compare these two data files and come to better results and speed?

Thanks