Re: select case insensitive

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 06 Mar 2007 22:00:53 +0100
Message-ID: <45EDD685.7090503@gmail.com>

Mariano schrieb:
> Hi to all, I need to execute a select query in case insentive way, for
> example:
> select * from table where name LIKE John
> should gave me all rows with John, JOHN, john and so on.
>
> I've read that usually it's necessary to execute the follow commands:
> alter session set nls_comp=ansi;
> alter session set nls_sort=binary_ci;
>
> Alas nothing changed, I've Oracle Database 10g Express Edition Release
> 10.2.0.1.0.
> Who can help me please?
>

SQL> select * from v$version;

BANNER

---

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production

SQL> alter session set nls_comp='LINGUISTIC';

Session altered.

SQL> alter session set nls_sort='BINARY_CI';

Session altered.

SQL> with t as (

   2  select 'John' john from dual union all
   3  select 'john' from dual union all
   4  select 'JOHN' from dual

   5 )
   6 select * from t where john like 'John'    7 ;

JOHN

---

John
john
JOHN Best regards

Maxim Received on Tue Mar 06 2007 - 15:00:53 CST