Search code examples
sqloracle-databasecase-sensitive

How to ignore case sensitivity in Oracle database query

I have string with upper and lower case characters, How can I ignore case sensitivity.

I have tried with COLLET,UPPERCASE,LOWERCASE It is not working. is there any other way to ignore?

Solution

  • Upper, and lower method are ok, but if you are looking for something more sophisticated try this. 

    create table table_test (a varchar2(100));

insert into table_test values( 'ABC');
insert into table_test values( 'abc');
insert into table_test values( 'AbC');

alter session set NLS_COMP=LINGUISTIC; 
-- how to compare string 
alter session set NLS_SORT=BINARY_AI;  --//or alter session set NLS_SORT=BINARY_CI;  
-- how to sort string  
-- the magic starts here 
    select * from table_test where a = 'ABC'

    And full description of different method of sorting and comparing: https://docs.oracle.com/cd/B28359_01/server.111/b28298/ch5lingsort.htm#CIHJBFAD