Oracle DB: How can I write query ignoring case?

Question

Hello,

As I had written in title, I have SQL query, run on Oracle DB, lets say:

SELECT * FROM TABLE WHERE TABLE.NAME Like 'IgNoReCaSe'

If I would like, that the query would return either "IGNORECASE", "ignorecase" or combinations of them, how can this be done?

Is this possible?

Answer 1

You can use either lower or upper function on both sides of the where condition

Answer 2

Select * from table where upper(table.name) like upper('IgNoreCaSe');

Alternatively, substitute lower for upper.

Answer 3

you can use the upper() function in your query, and to increase performance you can use a function-base index

 CREATE INDEX upper_index_name ON table(upper(name))

there is a lttle configuratin that may need to be done for these to work:

user level:

your db user will require 'query rewrite' and 'global query rewrite' priveleges

and two properties on the session or system level:

QUERY_REWRITE_ENABLED=TRUE and QUERY_REWRITE_INTEGRITY=TRUSTED

you can find some info here and here

Answer 4

You can use ALTER SESSION statements to set comparison to case-insensitive. See this FAQ.

alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=BINARY_CI;

Answer 5

...also do the conversion to upper or lower outside of the query:

tableName:= UPPER(someValue || '%');

...

Select * from table where upper(table.name) like tableName

Answer 6

You could also use Regular Expressions:

SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i');

Answer 7

Also don't forget the obvious, does the data in the tables need to have case? You could only insert rows already in lower case (or convert the existing DB rows to lower case) and be done with it right from the start.