I have a company database with name, address and city. I have been able to get rid of duplicates with matches at the beginning of the name with the same address, the same name and address, and the same name with the same beginning of the address.
But I now have some situations like
DiMio and Di Mio
P S Gourmet Coffee and Ps Gourmet Coffee
Both have the same addresses and city but there are too many cases of names being different with the same address that I can't just use an address match. Some part of the name needs to match also.
I have tried several things, including
SELECT id, name, address, city, count( * ) AS n
GROUP BY name like SUBSTRING(name FROM 5 FOR 4) , address, left( city, 4 )
HAVING n >1
But that didn't really work well at all.
Is there a way to do this in MySQL or do I need to do it with a php routine of some sort?