Re: using "WHERE" without case sensitivity

Problem with that, is it disables the use of the index because you have modified the left hand side of the where clause.

Can't have cake & eat it to!

John Ellard wrote:

> Steve,
>
> SELECT * FROM TABLE_NAME WHERE UPPER(NAME)
> = 'JOE'
>
> This will convert the field's contents to
> upper case first, then make the
> comparison. You can test this out using
> Oracle's SQL*Plus tool.
>
> Use Oracle's UPPER() or LOWER() functions
> to avoid case sensitivity. FYI, case
> sensitivity is a database option that can
> be set by your DBA.
>
> John Ellard
> jepcsstx_at_bga.com
>
> On Fri, 16 Jan 1998 11:12:53 -0500, Steven
> Schultz <sjschultz_at_home.com> wrote:
>
> >> Steven Schultz wrote in message
 <34BBDC97.415B_at_home.com>...
> >> >I want to be able to search for text
 within fields and select records
> >> >even if the case is not the same (i.e.
 "Joe" = "joe"). The only two
> >> >methods I have come up with is to add
 fields to the tables where the
> >> >data to be searched on has been made
 into all upper or all lower case
> >> >and convert the text being searched for
 into the same case or use the
> >> >upper or lower functions on both the
 fields and the text to be searched.
> >> >Neither option is a good one. What I
 would like is to be able to create
> >> >an index which converts all text to one
 case. Is there any capabilities
> >> >in Oracle which assists with this
 problem?
> >
> >I don't think I made my question was
 completely clear. The varchar2
> >value that I want to match in Oracle can
 be any combination of upper and
> >lower case, i.e., jOe, joE, JoE, etc.
Received on Tue Jan 20 1998 - 00:00:00 CET