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?
...also do the conversion to upper or lower outside of the query:
tableName:= UPPER(someValue || '%');
...
Select * from table where upper(table.name) like tableName
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))
You could also use Regular Expressions:
SELECT * FROM TABLE WHERE REGEXP_LIKE (TABLE.NAME,'IgNoReCaSe','i');
You can use either lower or upper function on both sides of the where condition
Select * from table where upper(table.name) like upper('IgNoreCaSe');
Alternatively, substitute lower for upper.
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');
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'
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.
Source: Stackoverflow.com