Case Insensitive Searching in Oracle

Case insensitive searching in Oracle

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

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:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

This information was taken from Oracle case insensitive searches. The article mentions REGEXP_LIKE but it seems to work with good old = as well.

---

In versions older than 10gR2 it can't really be done and the usual approach, if you don't need accent-insensitive search, is to just UPPER() both the column and the search expression.

How to ignore case sensitivity in Oracle database query

Upper, and lower method are ok, but if you are looking for something more sophisticated try this.

create table table_test (a varchar2(100));

insert into table_test values( 'ABC');
insert into table_test values( 'abc');
insert into table_test values( 'AbC');

alter session set NLS_COMP=LINGUISTIC; 
-- how to compare string 
alter session set NLS_SORT=BINARY_AI;  --//or alter session set NLS_SORT=BINARY_CI;  
-- how to sort string  
-- the magic starts here 
    select * from table_test where a = 'ABC'

And full description of different method of sorting and comparing:
https://docs.oracle.com/cd/B28359_01/server.111/b28298/ch5lingsort.htm#CIHJBFAD

Is it possible to perform a case insensitive search in LIKE statement in SQL?

I do not know what database you are using but if this were for Oracle then you could just force the case of both things. This though comes at a cost for execution times since it does it for all values in that column but you'd only see the cost if you have a lot of data and could work around that with a function based index. So something like this, again for Oracle:

AND UPPER(a.request_id) LIKE '%#UCase(Form.Searchbar)#%'

But I would suggest you use a queryparam since appears to come from a user inputted box, so:

AND UPPER(a.request_id) LIKE <cfqueryparam value="%#UCase(Form.Searchbar)#%" cfsqltype="cf_sql_varchar" />

Oracle DB: How can I write query ignoring case?

Use ALTER SESSION statements to set comparison to case-insensitive:

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

If you're still using version 10gR2, use the below statements. See this FAQ for details.

alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=BINARY_CI;

Performance of case-insensitive search in Oracle database

Yes, use of of UPPER(some_column)=UPPER(some_text) really is the best way, but you can create an index on UPPER(some_column). That should alleviate the problem.

---

Related Topics