Solved

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

Posted on 2004-08-26
8
1,670 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:candelaboy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 13

Accepted Solution

by:
riazpk earned 25 total points
ID: 11910032
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
 
LVL 13

Expert Comment

by:riazpk
ID: 11910046
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
 
LVL 8

Expert Comment

by:baonguyen1
ID: 11910357

Think you have to use upper anyway
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 13

Expert Comment

by:riazpk
ID: 11910754
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
 
LVL 22

Expert Comment

by:earth man2
ID: 11911477
Don't allow lower case to be stored in that column use update and insert trigger to convert it to upper case.
0
 
LVL 9

Expert Comment

by:konektor
ID: 11911702
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question