Case Insensitive Searching in APEX

By default Oracle (and inherently APEX) is case sensitive, i.e. MARTIN is not the same string as martin. This is apparent in both sorting (order by) and searching (where clauses).

In a previous article I covered how to do case insensitive sorting in APEX. Before continuing please read the article as this post builds upon it. This post shows how to do case insensitive searching by default in APEX (i.e. every time you have a where clause MARTIN is the same as martin).

Suppose an report's query is the following. No row's will be returned because all the jobs are uppercase. In this example the valid job name is CLERK not cLERk.

select  *
from emp
-- Note this could also be a page item like :P1_JOB
-- Where user types in job name
where job = 'cLERk'

To have all queries do case insensitive searching in APEX go to Shared Components > Globalization Attributes and change the following settings:

• Character Value Comparison: BINARY_CI

• Character Value Comparison Behavior: Linguistic

If you run the same report with the above query it will now return rows for all the CLERK jobs:

Reference Material

To learn more about each of the key APEX settings please click on the help icon as it covers what each option means. The settings reference NLS parameters NLS_SORT and NLS_COMP. You can find the default settings for each parameter using the query below. More information about these parameters can be found in the Oracle documentation for NLS parameters.

select *
from v$nls_parameters
where parameter in ('NLS_COMP', 'NLS_SORT')
;

PARAMETER VALUE  CON_ID 
--------- ------ ------ 
NLS_SORT  BINARY      0 
NLS_COMP  BINARY      0

If you don't want to enable case insensitive searching for your entire application but do want it on a specific query/report you can do so using collate binary_ci. Chris Saxon has a great article which goes into detail about the collate command.

select  *
from emp
where job = 'cLERk' collate binary_ci