Back
The default behavior of LIKE and the other comparison operators, =, etc is case-sensitive.
Is it possible to make them case-insensitive?
In Oracle 10gR2, you can fine-tune the behavior of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:
Query:
SQL> SET HEADING OFFSQL> SELECT * 2 FROM NLS_SESSION_PARAMETERS 3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');NLS_SORTBINARYNLS_COMPBINARYSQL>SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH 2 FROM DUAL; 0SQL>SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;Session altered.SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;Session altered.SQL>SQL> SELECT * 2 FROM NLS_SESSION_PARAMETERS 3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');NLS_SORTBINARY_CINLS_COMPLINGUISTICSQL>SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH 2 FROM DUAL; 1
SQL> SET HEADING OFF
SQL> SELECT *
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');
NLS_SORT
BINARY
NLS_COMP
SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
2 FROM DUAL;
0
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
Session altered.
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
BINARY_CI
LINGUISTIC
1
You can also create case insensitive indexes like this:
create index nlsci1_gen_personon MY_PERSON (NLSSORT (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI') );
create index
nlsci1_gen_person
on
MY_PERSON
(NLSSORT
(PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
);
31k questions
32.4k answers
501 comments
693 users