The Oracle database itself cannot be made case-inesnsitive. You have a
few options for doing case-insensitive queries, though

1) Create a function-based index on UPPER( column_name ). That's
generally the easiest and allows Oracle to use indexes to find the
column you're interested in.
2) Store data in a particular case (i.e. Oracle stores table names in
upper case in the data dictionary normally) enforced with an appropriate
constraint.
3) Store data twice, once in the case it is entered and once in upper
case using a trigger to populate the "shadow" uppercase column.
4) Use some of the NLS_SORT settings to do a case-insensitive search.
This can get painful rather quickly, though, since equality operators
and LIKE operators differ a bit in functionality. This also has the
performance implication of forcing full table scans.

Justin Cave <[Email address protected]
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: Benbart
[mailto:oracledba-ezmlmshield-x54187402.[Email address protected]
Sent: Saturday, June 04, 2005 12:52 AM
To: LazyDBA Discussion
Subject: RE: UPPER function ..

Hi Jon,



Thanks for your response, I want Oracle to do case-insensitive SELECTs



What am trying to do is, let's say a user entered names as John Doe1,
JOHN
Doe2, JoHN Doe3. If I run a SQL like SELECT * FROM CUSTOMERS WHERE
FIRSTNAME
= 'JOHN', it will only pick up JOHN Doe2. To select all three records, I
have to do SELECT * FROM CUSTOMERS WHERE UPPER(FIRSTNAME) = 'JOHN' or
SELECT
* FROM CUSTOMERS WHERE LOWER(FIRSTNAME) = 'john'



Even if let's say I have a CUSTOMER named mickey MOUSE and I do SELECT *
FROM CUSTOMERS WHERE FIRSTNAME = 'MICKEY', it does not get selected
because
the record is saved in the database as lower case mickey not MICKEY. I
remember reading somewhere to avoid using functions as much as possible
and
that is the only reason why I want to know whether Oracle can be
configured
to be case-insensitive.





-------Original Message-------



From: Knight Jon

Date: 05/30/05 10:05:44

To: [Email address protected]

Subject: RE: UPPER function ..



**A LazyDBA.com subscriber has responded to your lazydba.com post**

**LazyDBA.com mail shield has forwarded you this email,

**and removed any attachments, and kept your email address secret

**from this person, and any viruses/trojans.

**If you reply to this email, the person will see your email address as
normal

**Anything below this line is the original email text





When SELECTing, Oracle is already "case-sensitive". When using UPPER,

you're comparing regardless of case. But, I'm not sure exactly what
you're

trying to accomplish. Could you post an example?



Thanks,

Jon Knight



-----Original Message-----

From: Benbart

[Email address protected]

Sent: Sunday, May 29, 2005 2:30 AM

To: LazyDBA Discussion

Subject: UPPER function ..



Hi all,



How do I configure to be case insensitive so that I do not have to use
the

UPPER function?







--

No virus found in this outgoing message.

Checked by AVG Anti-Virus.

Version: 7.0.322 / Virus Database: 267.2.0 - Release Date: 27/05/2005







--------

website: http://www.LazyDBA.com

Please don't reply to RTFM questions

Oracle documentation is here: http://tahiti.oracle.com

To unsubscribe: see http://www.lazydba.com/unsubscribe.html

To subscribe: see http://www.lazydba.com

By using this list you agree to these

terms:http://www.lazydba.com/legal.html













--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page