Oracle DB: How can I write query ignoring case?

user_126781 0
Viewed 157997

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?

oracle sql database
9 Answers
Select * from table where upper(table.name) like upper('IgNoreCaSe');

Alternatively, substitute lower for upper.

user_932460

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;
devio_213360

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

user_1204570

You could also use Regular Expressions:

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

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))
user_1030430

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

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

...

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

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'
user_149066440

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.

user_686120
Related