How to make WHERE clause case insensitive in oracle sql database?

select * from table_Name where name ="red"

I need to fetch both “red” and “RED”.

For example: I need to use both upper and lower in same statement.

How can I do this?

Answer

You could use case insensitive parameter at session 'NLS_SORT=BINARY_CI'. There are two parameters at session level:

• NLS_COMP
• NLS_SORT

Let’s see a demo:

Normal scenario:

SQL> with names as
    (
     select 'Vishnu' name from dual
    )
    -- Your query starts here
    select * from names where name='vIsHnU';

no rows selected

Case insensitive approach:

SQL> alter session set nls_comp='LINGUISTIC';

Session altered

SQL> alter session set nls_sort='BINARY_CI';

Session altered

SQL> with names as
    (
     select 'Vishnu' name from dual
    )
    -- Your query starts here
    select * from names where name='vIsHnU';

NAME  
------
Vishnu

One more example:

SQL> with names as
    (
     select 'red' name from dual union all
     select 'RED' from dual
    )
    -- Your query starts here
    select * from names where name='rEd';

NAME
----
red
RED

To improve the performance, you could also create a case insensitive INDEX.

For example:

create index names_ci_indx on names(NLSSORT(name,'NLS_SORT=BINARY_CI'));

Now, there are ways to improve performance of above transaction. Please read Oracle – Case Insensitive Sorts & Compares