Re: question about stacking function calls in a where clause

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 27 Mar 2009 06:37:25 -0700 (PDT)
Message-ID: <6a65434a-5006-481e-9380-c684975cfedd_at_f19g2000vbf.googlegroups.com>

On Mar 27, 8: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?

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