Re: Case insensitive queries

In Article<NEWTNews.31708.827350033.fwalton_at_akcops.interpath.com>, <fwalton_at_akc.org> write:
> Path: news.NetVision.net.il!psinntp!psinntp!psinntp!howland.reston.ans.net!newsfeed.internetmci.com!in2.uu.net!news.interpath.net!usenet
> From: fwalton_at_akc.org
> Newsgroups: comp.databases.oracle
> Subject: Case insensitive queries
> Date: Wed, 20 Mar 96 11:21:20 PDT
> Organization: Interpath -- Providing Internet access to North Carolina
> Lines: 45
> Message-ID: <NEWTNews.31708.827350033.fwalton_at_akcops.interpath.com>
> NNTP-Posting-Host: akcops.pdial.interpath.net
> Mime-Version: 1.0
> Content-Type: TEXT/PLAIN; charset=US-ASCII
> X-Newsreader: NEWTNews & Chameleon -- TCP/IP for MS Windows from NetManage
>
>
> Folks:
>
> Can anyone help us out with the following?
>
> We are in process of evaluating Oracle and other databases. One
> of our requirements is for the database to store a mixture of
> upper, lower, and mixed case data, while allowing us to quickly
> and efficiently perform case insensitive queries against the
> data.
>
> We would like to order the queries as if the data was of one
> case. Upper, lower, and mixed cases would have the same sort
> value and would compare as equal. For example, the following
> would appear after a query on all names which begin with "dev".
>
> Devan
> DeVito
> deVoe
> Devun
>
> Note that the uppercase "V" is ordered the same as the lowercase
> "v", and the uppercase "D" is ordered the same as the lowercase
> "d".
>
> DB2 and Sybase handle this situation internally. Oracle's
> response is to create a second column that holds an uppercase
> version of last name, query against the second indexed field, but
> use the original field to display the result to the end user.
>
> Oracle's method requires us to duplicate every column that
> contains mixed case -- which in our shop is a lot of columns,
> resulting in a large database and possibly poor performance
>
> Does anyone else have this same mixed case requirement? If so,
> how do you provide case insensitive support in Oracle?
>
> As I have limited access to this newsgroup, please email any
> comments to:
>
> Frank H. Walton
> fwalton_at_akc.org
> 919-233-3610
>
>

you don't have to realy CREATE the columns, you can user the UPPER function like this

: select UPPER(name) up_name ,name real_name
: from table
: where UPPER(name) like 'DEV%' 
: order by upper(name)

now all you have to do is not to show the first columns when in sql*plus just add

: col up_name no_print

good luck Received on Sun Mar 24 1996 - 00:00:00 CET