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,665 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
Getting a return value from an IN OUT parameter in Oracle? 7 45
history tablespace temp usage 2 31
oracle 11g 23 51
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now