I seem to recall some discussions in the past about various algorithms
that would help identify duplicate customers names or some such field where
you can't depend on an exact match.
I just threw this together in iSeries Nav Run SQL scripts. It relys upon the
soundex function of SQL to help with the task:
drop table miketest.customers;
create table miketest.customers like v84files#.mscmp100;
INSERT INTO MIKETEST.CUSTOMERS SELECT * FROM v84files#.mscmp100 WHERE
activ='1';
ALTER TABLE MIKETEST.CUSTOMERS ADD COLUMN SOUNDS FOR COLUMN SOUNDSLIKE
CHARACTER (4 ) NOT NULL WITH DEFAULT ' ';
update miketest.customers set sounds=soundex(cname);
with dups as (select sounds,count(*) from miketest.customers group by sounds
having count(*)>1) select a.sounds,a.cname,a.cusno,a.* from
miketest.customers a where a.sounds in (select sounds from dups) order by
a.sounds,a.cname,a.cusno;
In English...
Drop the temporary table.
Create a temporary table.
Insert all active customer records into temporary table.
Add a column for the SOUNDEX match.
Create the SOUNDEX field for each customer name.
Use a common table expression to find the duplicate SOUNDEX records to find
all the records with SOUNDEX matches.
This method didn't result in any earth shattering results for me (way too
results for one thing). But you should try it on your system and see how it
looks.
Mike Krebs
midrange.com
