Oracle DB: How can I write query ignoring case?
SqlDatabaseOracleSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
Select * from table where upper(table.name) like upper('IgNoreCaSe');
Alternatively, substitute lower for upper.
Solution 2 - Sql
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;
Solution 3 - Sql
You can use either lower or upper function on both sides of the where condition
Solution 4 - Sql
You could also use Regular Expressions:
SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i');
Solution 5 - Sql
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))
Solution 6 - Sql
You can convert both values to upper or lowercase using the upper or lower functions:
Select * from table where upper(table.name) like upper('IgNoreCaSe')
or
Select * from table where lower(table.name) like lower('IgNoreCaSe');
Solution 7 - Sql
...also do the conversion to upper or lower outside of the query:
tableName:= UPPER(someValue || '%');
...
Select * from table where upper(table.name) like tableName
Solution 8 - Sql
In version 12.2 and above, the simplest way to make the query case insensitive is this:
SELECT * FROM TABLE WHERE TABLE.NAME COLLATE BINARY_CI Like 'IgNoReCaSe'
Solution 9 - Sql
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.