How can set to don't differ capital letter from small letter when using "where col = 'a'" in oracle9i?

My query sql is:

select * from table
where col = 'a'

But my purpose is that results is both col is 'a' and col is 'A'.
So I modify it :

select * from table
where upper(col) = 'A'
It works correctly.

But I don't know whether it can be setted in Oracle 9i db system to  don't differ both them.
candelaboyAsked:
Who is Participating?
 
riazpkConnect With a Mentor Commented:
NO.........it can't be even done with Oracle 10g  (I mean case insensitive search). But you can use Oracle Intermedia Text to get the same effect OR you can use Function-besed index if you want to speed up the searh. Read about Function-Based Indexes at:

http://asktom.oracle.com/~tkyte/article1/index.html

To know about Oracle Intermedia Text (alongwith examples), visit:

http://asktom.oracle.com/pls/ask/wwv_flow.accept

and search for "intermedia text" ........tons of examples.
0
 
riazpkCommented:
ohhhh..........i am feeling now that the Oracle Intermedia Text wouldn't be so good for your case.............Function-Based Indexe would be great (or you can maitain a mirror column containing the value of upper(col))
0
 
baonguyen1Commented:

Think you have to use upper anyway
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
riazpkCommented:
However, this may be possible in Oracle 10g:

Here is example from asktom.oracle.com

ops$tkyte@ORA10G> create table t ( data varchar2(20) );
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into t values ( 'Hello' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'HeLlO' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'HELLO' );
 
1 row created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index t_idx on
  2  t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );
 
Index created.
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> variable x varchar2(25)
ops$tkyte@ORA10G> exec :x := 'hello';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t where data = :x;
 
no rows selected
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter session set nls_comp=ansi;
 
Session altered.
 
ops$tkyte@ORA10G> alter session set nls_sort=binary_ci;
 
Session altered.
 
ops$tkyte@ORA10G> select * from t where data = :x;
 
DATA
--------------------
Hello
HeLlO
HELLO
 
ops$tkyte@ORA10G> pause
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace on
ops$tkyte@ORA10G> select /*+ first_rows */ * from t where data = :x;
 
DATA
--------------------
Hello
HeLlO
HELLO
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2 Card=1 Bytes=12)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1
Bytes=12)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
0
 
earth man2Commented:
Don't allow lower case to be stored in that column use update and insert trigger to convert it to upper case.
0
 
konektorCommented:
your query will work, but if u have an index on column, query will not use it. use :
select * from table where col in ('a','A')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.