Re: Case Sensitivity in select

A copy of this was sent to Prakash <venkatprakash_at_hotmail.com> (if that email address didn't require changing) On Fri, 27 Aug 1999 00:40:32 GMT, you wrote:

>Use set SQLCASE upper;
>
>now select 'hello' from dual will retun
>
>HELLO
>

that will not do it. sqlcase is a SQLPLUS'ism that rewrites a query -- it does not affect the data being searched. given the question:

>> > case insensitive. i.e. so that select * from words where
>word='hello'
>> > will bring back records where word='HELLO' or 'Hello'.
>>

sqlcase will *not* fit the bill. consider this example:

tkyte_at_8i> create table t ( x varchar2(1) ); Table created.

tkyte_at_8i> insert into t values ( 'a' ); 1 row created.

tkyte_at_8i> insert into t values ( 'A' ); 1 row created.

tkyte_at_8i> set sqlcase upper

tkyte_at_8i> select * from t where x = 'a';

X
-
A

tkyte_at_8i> select * from t where x = 'A';

X
-
A

All sqlcase will do is rewrite the entire query, including constants, in upper case. it does not do a case insensitive search.
>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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 26 1999 - 21:08:56 CDT