0 votes
1 view
in SQL by (20.3k points)

The default behavior of LIKE and the other comparison operators, =, etc is case-sensitive.

Is it possible to make them case-insensitive?

1 Answer

0 votes
by (40.3k points)

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 OFF

SQL> SELECT *

  2  FROM NLS_SESSION_PARAMETERS

  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT

BINARY

NLS_COMP

BINARY

SQL>

SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH

  2  FROM DUAL;

         0

SQL>

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_SORT

BINARY_CI

NLS_COMP

LINGUISTIC

SQL>

SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH

  2  FROM DUAL;

         1

You can also create case insensitive indexes like this:

Query:

create index

   nlsci1_gen_person

on

   MY_PERSON

   (NLSSORT

      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')

   );

Related questions

0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...