Re: question about stacking function calls in a where clause

From: Mark D Powell <>
Date: Fri, 27 Mar 2009 06:37:25 -0700 (PDT)
Message-ID: <>

On Mar 27, 8:42 am, "Larry W. Virden" <> 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
> 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?

What version of Oracle?

On 10g plus you have the regular expression functions that could be used. These functions have a case insensitive option.

On all versions you have the replace function which could be used to remove the pesky apostrophe from the one column.

HTH -- Mark D Powell -- Received on Fri Mar 27 2009 - 08:37:25 CDT

Original text of this message