Case sensitive

[NickyB]

Is it possible to set Oracle (version 7) to be case insensitive, so records would be found in the database for the following if the field t_name contained 'TEST':

SELECT * FROM T1 WHERE t_name = 'test'
SELECT * FROM T1 WHERE t_name = 'Test'
SELECT * FROM T1 WHERE t_name = 'TEST'

I know that I can work round this by converting all my SQL comparisons using UPPER, but I am convinced there must be another way.

 

[Naith]

Yes, there is. Lower, and Initcap.

Sorry if that's fascecious, but the truth of the matter is Oracle 7 doesn't have any other way to address what you're trying to do.

Some databases have an "ignore case" flag that can be set for the entire database. Oracle does not, and thus case-insensitive queries have long caused problems, not with coding them, but with their performance (since indexes are typically not used to determine the result until 8i).