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:48:24 -0700 (PDT)
Message-ID: <47299f15-85be-43cf-bd90-67df0751976f_at_q2g2000vbr.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?
>
> 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 --- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 27 Mar 2009 06:48:24 -0700 (PDT)
Message-ID: <47299f15-85be-43cf-bd90-67df0751976f_at_q2g2000vbr.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?
>
> 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 --- Hide quoted text -
>
> - Show quoted text -
Sorry did not finish rewording the comment on replace before hitting send. I just wanted to point out replace can be used to convert a character to null effectively removing it from a value which might be helpful for some data.
- Mark D Powell --