Re: question about stacking function calls in a where clause

From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 27 Mar 2009 06:36:52 -0700 (PDT)
Message-ID: <4f1f9b30-f402-4556-a78d-fd50f8efc160_at_l19g2000vba.googlegroups.com>

On Mar 27, 7:42 am, "Larry W. Virden" <lvir..._at_gmail.com> wrote:
> I have a situation where I'm trying to compare information in two
> different columns in a where clause. The information in question are
> last names.
>
> In column 1, the information is stored in mixed case. So it might
> contain
>
> Jones
> Smith
> O'Malley
>
> In column 2, the name was entered via software which caused the
> letters to be upper case, and did not permit entry of special
> characters like the apostrophe. So its corresponding values would be
>
> JONES
> SMITH
> O MALLEY
>
> The where expression I am trying to get to work is
>
> (upper(table1.last_name,'''','_') not like table2.last_name)
>
> (this is just one part of a more complicated condition statement)
>
> This produces nearly the results I want - except for that pesky
> apostrophe.
>
> So I tried
> (upper(replace(table1.last_name,'''','_')) not like table2.last_name)
>
> but I do not see any difference in the items returned.
>
> Is there some other techniques needed for this type of thing?

Are you replacing apostrophe ' with underscore _ ?

Playing around with the functions in DUAL might give a clue. maybe if you posted a few of the failing cases, we might see something you haven't yet. (Sometimes just trying to reduce it to the simplest case shows the cause.)

And since you really are going one for one, the TRANSLATE function might work as well (or as poorly).

HTH, but let us know how it turns out.

   Ed Received on Fri Mar 27 2009 - 08:36:52 CDT