Re: case insensitive searches

cfetters_at_iac.net (Chuck Fetters) wrote:
>Randy Dewoolfson (randyd_at_cais2.cais.com) wrote:
>: Anthony Sama (asama_at_netaxs.com) wrote:
 

>: : 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
>cfetters_at_iac.net
>

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
lrohrer_at_earthlink.net
http://www.earthlink.net/~lrohrer/index.html Received on Mon Jul 24 1995 - 00:00:00 CEST