question about stacking function calls in a where clause

#1

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?

#2

On Mar 27, 7:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:

Quote:

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?

Are you replacing apostrophe ' with underscore _ ?

Playing around with the functions in DUAL might give a clue. maybe if
you posted a few of the failing cases, we might see something you
haven't yet. (Sometimes just trying to reduce it to the simplest case
shows the cause.)

And since you really are going one for one, the TRANSLATE function
might work as well (or as poorly).

HTH, but let us know how it turns out.
Ed

#3

On Mar 27, 8:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:

Quote:

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 --

#4

On Mar 27, 9:37*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On Mar 27, 8:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) 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 --

#5

On Mar 27, 7:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:

Quote:

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.

A couple other comments:

Quote:

The where expression I am trying to get to work is

(upper(table1.last_name,'''','_') not like table2.last_name)

UPPER() takes only one argument. This can't work. You should have
gotten an error.

Quote:

Is there some other techniques needed for this type of thing?

last thought: white space
Is there trailing white space on either name?
Does the table2 'O MALLEY' value have one or two spaces between the O
and the M??

Ed

#6

On Mar 27, 8:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:

Quote:

The where expression I am trying to get to work is

(upper(table1.last_name,'''','_') not like table2.last_name)

Sorry - I mistakenly copied the wrong string there - the string that
does work, sort of, is:
(upper(table1.last_name) not like table2.last_name)

#7

On Mar 27, 9:36*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Mar 27, 7:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:

So I tried
( (upper(table1.last_name,'''','_') not like table2.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?

Are you replacing apostrophe ' *with underscore _ ?

duh - your comment just made me wake up here. I have the arguments in
the where clause backwards - I want
(table2.last_name not like (upper(table1.last_name,'''','_'))

Sigh - sorry about that. I just ran a quick test and getting things in
the right order fixed my select up. Thank you!

Quote:

And since you really are going one for one, the TRANSLATE function
might work as well (or as poorly).

I was using replace because in the web page of info, the "r" came
before the "t" <blush>

Well, it seems to be working nicely now.

Thanks for all your help!

#8

On Mar 27, 9:37*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:

Quote:

On Mar 27, 8:42*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) 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

Quote:

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.

#9

On Mar 27, 9:48*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

last thought: white space
Is there trailing white space on either name?
Does the table2 'O MALLEY' value have one or two spaces between the O
and the M??

Good point! I just posted what my actual issue was - but you do point
out a good point. That earlier mention of a regular expression
function is sounding better and better.

Then I might be able to say something like [' ]* for the match.

Hmm.

I really appreciate everyone's comments.

#10

On Mar 27, 11:15*am, "Larry W. Virden" <lvir... (AT) gmail (DOT) com> wrote:

Quote:

On Mar 27, 9:48*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

last thought: white space
Is there trailing white space on either name?
Does the table2 'O MALLEY' value have one or two spaces between the O
and the M??

Good point! I just posted what my actual issue was - but you do point
out a good point. That earlier mention of a regular expression
function is sounding better and better.

Then I might be able to say something like [' ]* for the match.

Hmm.

I really appreciate everyone's comments.

And we really appreciate feedback, especially the grateful kind!!

Have a great day all!
Ed

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

question about stacking function calls in a where clause

comp.databases.oracle.misc comp.databases.oracle.misc

question about stacking function calls in a where clause - 03-27-2009 , 06:42 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 07:36 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 07:37 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 07:48 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 07:48 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 09:06 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 09:11 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 09:13 AM

Re: question about stacking function calls in a where clause - 03-27-2009 , 09:15 AM

Re: question about stacking function calls in a where clause - 03-28-2009 , 06:48 AM