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