Re: Case Sensitivity in select

Use set SQLCASE upper;

now select 'hello' from dual will retun

HELLO Thanks

V Prakash

In article <37C537BE.9E3F9C1D_at_beusen.de>,   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
>
> --
> ---------------------------------------------------------------
> Dipl.-Inf. (FH) Stephan Born | beusen Consulting GmbH
> fon: +49 30 549932-17 | Landsberger Allee 392
> fax: +49 30 549932-29 | 12681 Berlin
> mailto:stephan.born_at_beusen.de | Germany
> ---------------------------------------------------------------
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 26 1999 - 19:40:32 CDT