question about stacking function calls in a where clause

From: Larry W. Virden <lvirden_at_gmail.com>
Date: Fri, 27 Mar 2009 05:42:03 -0700 (PDT)
Message-ID: <2117a6cb-4d95-45d1-abb3-9c2b5df48456_at_42g2000yqk.googlegroups.com>

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? Received on Fri Mar 27 2009 - 07:42:03 CDT