Re: SQL*Plus /Oracle Forms

In some cases you won't be able to do it, For example, how would you know that Long-Smith and Smith are really the same people? If last name is all you have to go on, then there is no way except to call the Psychic Hotline.

On the other hand, if they are spelling errors, you might be able to generate a report like
Select a.lastname,b.lastname
from mastertable a, othertable b
where upper(substr(a.lastname,1,3)) = upper(substr(b.lastname,1,3)) and a.lastname <> b.lastname;

That should give you a list where the first 3 characters of the last name match, but it is not a complete match. You can then eyeball the report and maybe catch a lot of spelling errors. If this yields too much data, go for the first 4 characters, or some other algorithm.

Mike

"Patterson" <r_patterson_at_cox-internet.com> wrote in message news:tceljp8ookg89c_at_corp.supernews.com...
> How can I compare last name for people in two different tables?
>
> A problem arises when people have a hyphenated name:
> for example, in one table the last name may be Long-Smith
> and in the other table it may be just Smith.
>
> Another problem that I've run into is if the person's name is mispelled in
> one table:
> for example, in one table the last name may be Christopher and mispelled
 in
> the other table as Christophe
>
> What I am trying to do is clean up the "bad" table. One table is
 updloaded
> monthly so the data in that table is "official". However, in the other
 table
> a user can add people to it (which may duplicate a person because they
> didn't see the name in the LOV the way they wanted it displayed (such as
 an
> alias name)
>
>
>
>
Received on Mon Apr 02 2001 - 03:19:41 CEST