CODEWITHSUNDEEP
sqloracle-databaseoracle10gwhere-clausecase-insensitive
Vishnu K
Vishnu K

Reputation: 58

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?

Upvotes: 1

Views: 386

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48111

select * from table_Name where lower(name) ="red"

or

select * from table_Name where upper(name) ="RED"

This cannot use an index on name. It might be appropriate to create a function based index on the expression used.

Upvotes: 0

Related Questions