Link to home
Start Free TrialLog in
Avatar of fpinheiro
fpinheiro

asked on

CASE INSENSITIVE ON ORACLE 10G

How to set a oracle 10g database to case-insensitive at database level ?
Avatar of Sean Stuber
Sean Stuber

sorry, you can't


Oracle Text would be as case insensitive as you can get.  That allows case insensitive searching, but does require some setup and maintenance.
yes,agreed, but even that is a full "database" level change.  Oracle Text would only apply to data stored within the context of that feature.
Yes.  I just wasn't sure what exactly they are looking for and wanted to put it out there as an option.  A lot of times people are looking for case insenstive searching.

As far as sql statements go, oracle is case insensitive, as long as you did not create the column names in mixed or lower case on purpose, which most people do not.

select test_col from test_tab;

yields the same results as

SELECT TEST_COL FROM TEST_TAB;
yes,  fpinheiro, johnsone brings up a good point.  Case sensitivity is relative to what you are doing.

Or maybe that's the point of your question.

Can you elaborate?
Avatar of fpinheiro

ASKER

I need case-insensitive for the data in tables, not in name objects.

like this
in the select above
select name from table where name = 'Robert'

possible results are:
ROBERT
robert
Robert
RoBeRt



Then you would need Oracle Text.  A Oracle Text index would need to be created on each column that you wanted case insensitive searching on.

The only other way would be to create function based indexes on UPPER(<col>) and then in all you queries specify WHERE UPPER(<col>) = UPPER('Robert').
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Its not exactly what I wanted, but is a good alternated solution.