Re: question about stacking function calls in a where clause
From: Larry W. Virden <lvirden_at_gmail.com>
Date: Fri, 27 Mar 2009 08:13:42 -0700 (PDT)
Message-ID: <c6357573-233d-491c-a1c1-ea23b7942728_at_e10g2000vbe.googlegroups.com>
On Mar 27, 9:37 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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?
Date: Fri, 27 Mar 2009 08:13:42 -0700 (PDT)
Message-ID: <c6357573-233d-491c-a1c1-ea23b7942728_at_e10g2000vbe.googlegroups.com>
On Mar 27, 9:37 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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?
Oracle 10
>
> 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.
I thought I would just change the apostrophe into a 1 character
wildcard to avoid the issue.
Though your mention of some sort of regular expression funtion is
intriguing - I'd be tempted to do some sort of conditional check for 0
or 1 occurance of the ' - which would allow me to treat O'Malley, O
Malley, and OMalley all the same.
Received on Fri Mar 27 2009 - 10:13:42 CDT