Ask the Expert

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. 

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.

    Requires Free Membership to View

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              

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: