Re: Case Sensitivity in select

A copy of this was sent to "Prakash V" <venkatprakash_at_hotmail.com> (if that email address didn't require changing) On Thu, 26 Aug 1999 17:13:16 GMT, you wrote:

>Sure. Here is the syntax for that:
>
>SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
>
>And the example:
>
>SQL> set sqlcase upper
>SQL> select 'hello' from dual;
>
>'HELL
>-----
>HELLO
>
>1 row selected.
>
>SQL> select 'hEllo' from dual;
>
>'HELL
>-----
>HELLO
>
>Hope this will do.
>

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: "how to have a case insensitive query", 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
>
>1 row selected.
>
>
>______________________________________________________
>Get Your Private, Free Email at http://www.hotmail.com
>
>
> 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