>Standardize all the Geocode stuff to fixed fields in a standard format; Apartment to Apt, Lane to LN, Street to ST. Break the zip code into two pieces. Then do your record linkage.
>
>
>>I inherited a project from someone who wrote a long convoluted program to look for duplicates in a table. I'm wondering if it could be done easier using SQL. Here's an example of the problem we have frequently:
>>
>>Record 1
>>------------------------
>>JOHN
>>L
>>SMITH
>>1234 ANYWHERE LANE
>>HERESVILLE
>>PA
>>16555
>>------------------------
>>
>>Record 2
>>------------------------
>>JOHN
>>
>>SMITH
>>1234 ANYWHERE LN
>>HERESVILLE
>>PA
>>16555-2345
>>------------------------
>>
>>Records 1 and 2 are the same person, but a SELECT DISTINCT does not eliminate one of them because the middle initial, address line, and zip code are not identical. My question is this, is there a way with SQL to identify these two records, which are in the same file, as "possible" duplicates which I could then display to a user for them to decide what to do with them?
>>
>>Thanks.

Or go a partial combination on first portions of critical fields then an eyeball approach. (you can still try to pre-clean the data as Raymond mentioned.

select ;
    upper( left( lastname, 10 ) +;
         left( firstname, 10 )+;
         left( address1, 20 ) +;
         left( city, 10 ) +;
         left( state, 2 )), count(*);
  from SrcTable;
  group by 1;
  having count(*) > 1;
  into cursor PossibleDups