Q

Duplicate company names

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close