Oracle Case insensitive search

Data entered in table column could be in different cases lower/upper making it difficult for search operation to retrieve all possible matches from the table

e.g.  To search abcd from the table if you enter Abcd as input string or anything that does not match with cases in which data is stored will result in “no rows selected” & vice versa.

Suppose case table has below rows, each one with different case but similar string

SQL> select * from case;

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

Objective is to get all 6 rows irrespective of the case of input string in search

A.  Case sensitive search:

In case sensitive search which is default, if case is matched then only results will be retrieved

SQL> select * from case where TEXT='Abcd';

TEXT

--------------------------------------------------------------------------------

Abcd

1 row selected.

SQL> select * from case where TEXT like 'ab%';

TEXT

--------------------------------------------------------------------------------

abCd

abcD

abcd

3 rows selected.

SQL> select * from case where TEXT like 'Ab%';

TEXT

--------------------------------------------------------------------------------

Abcd

1 row selected.

B. Case insensitive search:

In case insensitive search irrespective of case mentioned in search matching rows will be retrieve.

There are Four Methods to achieve oracle case insensitive search

 

Method 1:

Using lower or upper sql function to convert column values to match lower or upper input search string.

Lower or upper function can be applied to column on left side of the search clause but it requires to pass input w.r.t function used i.e. if lower function used then input string should be lower only

 

Using lower/upper function on columns will convert the case accordingly on fly to match input string & retrieve the values irrespective of the case

SQL> select * from case where lower(TEXT) like 'ab%';

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

SQL> select * from case where upper(TEXT) like 'AB%';

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

Method 2: 

Using lower or upper sql function to convert column values as well as input string to same case.

This method has a benefit over method 1 is that input string can be of any case as function conversion is done at both side of the clause.

 

But disadvantage is that various application developer tools does not allow to apply function on right side of the where clause as where condition getting formed in runtime as per user’s selection. 

Refer Method 3 for this situation

SQL> select * from case where lower(TEXT) like lower('Ab%');

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

SQL> select * from case where upper(TEXT) like upper('Ab%');

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

Method 3:

Using REGEXP_LIKE to convert both column as well as input string using REGEXP function.

 

Its best possible way for case insensitive search , just need to add function which includes both column as well input string without any ‘%’  

SQL>  select * from case where  REGEXP_LIKE(TEXT,'Ab','i');

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

SQL>  select * from case where  REGEXP_LIKE(TEXT,'aB','i');

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.

Method 4:

Use NLS_COMP & NLS_SORT parameters to ignore case sensitivity at session of system level

SQL> select * from case where text like 'Ab%';

TEXT

--------------------------------------------------------------------------------

Abcd

1 row selected.

SQL>  ALTER SESSION SET NLS_COMP=LINGUISTIC ;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_AI  ;

Session altered.

SQL> select * from case where text like 'Ab%';

TEXT

--------------------------------------------------------------------------------

Abcd

aBcd

abCd

abcD

abcd

ABCD

6 rows selected.