case insensitive searches

case insensitive searches

Post by Anthony Sa » Thu, 20 Jul 1995 04:00:00



What's the best way to specify case insensivity in a WHERE
clause?

--
---------------------------------------------------------------------------

Anthony Sama

http://www.netaxs.com/~asama

 
 
 

case insensitive searches

Post by Randy Dewoolfs » Thu, 20 Jul 1995 04:00:00


: What's the best way to specify case insensivity in a WHERE
: clause?

To match strings withou regard to case you might try
WHERE UPPER( string1) = UPPER( string2 )
or the LOWER equivalent..

Hope it helps
Randy :)

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I



  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I

 
 
 

case insensitive searches

Post by Chuck Fette » Mon, 24 Jul 1995 04:00:00



: : What's the best way to specify case insensivity in a WHERE
: : clause?

: To match strings withou regard to case you might try
: WHERE UPPER( string1) = UPPER( string2 )
: or the LOWER equivalent..

: Hope it helps
: Randy :)

It is true that using upper or lower will provide a case
insensitive search.  However, if either string1 or string2
is a column name, then Oracle will have to do a sequential
search on the table because it hase to change the column
value to upper or lower case before doing the comparision.
If the table is large this could be very slow.
Carol Fetters

 
 
 

case insensitive searches

Post by Lawrence V. Rohre » Tue, 25 Jul 1995 04:00:00





>: : What's the best way to specify case insensivity in a WHERE
>: : clause?

>: To match strings withou regard to case you might try
>: WHERE UPPER( string1) = UPPER( string2 )
>: or the LOWER equivalent..

>: Hope it helps
>: Randy :)

>It is true that using upper or lower will provide a case
>insensitive search.  However, if either string1 or string2
>is a column name, then Oracle will have to do a sequential
>search on the table because it hase to change the column
>value to upper or lower case before doing the comparision.
>If the table is large this could be very slow.
>Carol Fetters


So It would seem the best INDEXABLE/fast method would be to store your
text strings converted (UPPER or LOWER).

As a rule I follow as a Oracle software architect; coded fields are
always in upper case.  -- Force the screen application to convert the
users typing. It saves a lot of confusion -- both programmer and user's.

Now if these "strings" are actually textual descriptions ie paragraphs:
I would question the use of tweezers to do a sledghammer's job. Yes,
Oracle has some text comparison operators (like, =, soudex, upper/lower,
etc.) The real issue is what do you really need in queries? I could
forsee using Oracle to filter out useless words and store the important
ones, provide a Database of references, query the references and display
the text...

It is late at night. I best not think of such things...

(What about long text fields; Oracle's operators don't work so well)
(What about.... zzzzzzzz)

BTW soundex may be of some assitance too.

Lawrence V. Rohrer

http://www.earthlink.net/~lrohrer/index.html

 
 
 

case insensitive searches

Post by Richard Ave » Tue, 08 Aug 1995 04:00:00





> : What's the best way to specify case insensivity in a WHERE
> : clause?

> To match strings withou regard to case you might try
> WHERE UPPER( string1) = UPPER( string2 )
> or the LOWER equivalent..

[SNIP]

You may want to bear in mind that the upper function negates the use of any
index on a column! One way round this would be to force the entry of the
field to upper case, or where this is not appropriate you could denormalise
another column into the table holding the same value in upper case for
query purposes.

Richard Avery
--
The above posting does not represent the views of Nortel in any way.