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
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,667 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 video shows how to recover a database from a user managed backup

856 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