# 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](https://talkapex.com/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`.

```sql
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`
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1704197808376/b3a1fc99-61b7-4666-89fa-7634abfa79e9.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1704197872443/dcf2de80-9d8e-4659-9b32-14c90f40c551.png align="center")

## 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](https://docs.oracle.com/en/database/oracle/oracle-database/21/nlspg/setting-up-globalization-support-environment.html#GUID-60ED0CB2-50AB-4BF4-8FAF-04B79A400329).

```sql
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](https://twitter.com/ChrisRSaxon) has a [great article](https://blogs.oracle.com/sql/post/how-to-do-case-insensitive-and-accent-insensitive-search-in-oracle-database) which goes into detail about the `collate` command.

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