Oracle DB: How can I write query ignoring case?

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?


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

Alternatively, substitute lower for upper.


Use ALTER SESSION statements to set comparison to case-insensitive:

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

If you're still using version 10gR2, use the below statements. See this FAQ for details.

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

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