Re: Case Sensitivity in select

From: Dante <dnotari_at_yahoo.com>
Date: 1999/09/01
Message-ID: <cB=NN0+XmBH1wWhOIiUqDA4pJcQ3_at_4ax.com>#1/1


Stephan,
with Oracle 8i you can use a functionbased index in order to have an index used based on a function.

eg. CREATE INDEX upper_name ON emp (UPPER(name));

would cause a

SELECT * FROM emp where UPPER(name) = UPPER('Stephen');

to use the index.

Regards
Dante

On Thu, 26 Aug 1999 14:49:02 +0200, sborn_at_beusen.de wrote:

>
>
>Stu schrieb:
>
>> Is it possible to set a parameter which will make a select statement
>> case insensitive. i.e. so that select * from words where word='hello'
>> will bring back records where word='HELLO' or 'Hello'.
>
>You can do the following
>
>select * from words where lower(word) = 'hello'
>
>or
>
>select * from words where lower(word) = lower('Hello')
>
>But I think that you will always have full scans because you are using
>functions.
>
>If this is the only column you will search case insensitive, you can add
>a new
>column to this table, which will be filled by a trigger with lower case
>versions of this word.
>Then you can create a non-unique index on that column.
>
>Now statements like
>
>select * from words where ci_word = lower('Hello')
>
>use the proper index.
>
>I hope this will help you, let me know.
>
>Regards, Stephan
Received on Wed Sep 01 1999 - 00:00:00 CEST

Original text of this message