I am trying to match some records by using a SELECT statement with conditions, UNION with another SELECT statement with conditions. I have in total about four or five union steps. I get a results table with duplicate records such as these (showing just the wo fields that cause the duplicates):
Assetno Company 123467899 FREEDMAN ROSS & CO. 343234344 FREEDMAN ROSS INVESTMENT
These are the same records but I would like to eliminate one of them by comparing the company names. I am trying to do this using an SQL cursor on the table but not sure whether it's the right way. Any suggestions or examples would be appreciated.
This question has a simple answer: if you can define the rule by which you know that it's the same company, then you can write a query, or series of queries, to implement that rule. Generally speaking, you can probably do it with SQL, and not use a cursor (because cursors are, in general, slower than queries).
Your main problem is that you will have a hard time finding such a rule. The example company names that you gave would lend themselves to a rule like comparing the first 13 characters of the company name. But this same rule would say that these companies are the same, too:
Assetno Company 112233445 INTERNATIONAL NATURAL FOODS CO. 223344556 INTERNATIONAL PESTICIDES CORP.
Your best bet is to simply sort the companies by name, and then have a human being review them.
The human being should also be on the lookout for companies like these:
Assetno Company 331144225 T.B.L. CORPORATION 553344221 THE BEST LAWYERS CORP.
So sometimes they're the same company but the names are wildly different. No rule is going to find those, so you have to have a human being go over the list anyway.
This was first published in October 2004