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,668 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

740 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