RE: How can we make any column of a table as case insensitive

It's not clear to me that putting the data in uniform case (e.g., all upper or all lower) will be equivalent to making it case-insensitive. Unless you can count on the criteria applied to the column always being in that same case, you could still have 'false' mismatches.

The only thing that comes to my mind is rolling your own querying app--I don't suppose that appeals? But probably somebody else will have a better idea...

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----
Sent: Tuesday, April 16, 2002 11:48 AM
To: Multiple recipients of list ORACLE-L

Create a view where you select the case-insensitive columns with upper(col-name), then create a public synonym on the view. The application will "see" the table through the view.  

                    "Mandal,

                    Ashoke"              To:     Multiple recipients of list
ORACLE-L      
                    <ashoke.k.man        <ORACLE-L_at_fatcity.com>

                    dal                  cc:

                    @medtronic.co        Subject:     How can we make any
column of a      
                    m>                   table as case insensitive

                    Sent by: root

 

 

                    04/16/2002

                    12:33 PM

                    Please

                    respond to

                    ORACLE-L

 

 

Greetings All,

We have a requirement of making one or multiple or all columns of a table case insensitive. We can not modify the query as it comes from a 3rd party application. One option I can think of is that introduce a trigger on the table and before inserting or modifying the column's data force it to be all uppercase or all lowercase.

Is there any other way of doing it? Can we do it using a constraint?

This requirement is like the feature of ic(ignore case) in vi editor.

Thanks,
Ashoke

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
  INET: ashoke.k.mandal_at_medtronic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pardee, Roy E
  INET: roy.e.pardee_at_lmco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).