Oracle Db: How to Write Query Ignoring Case

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;

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

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.

Ignore Case In Query

You mean lower() and upper() right?

Oracle supports many different languages, which means you need to use a nls function. In this case nls_lower.

select nls_lower('my string', 'NLS_SORT = ARABIC') from dual

This may cause problems with latin characters - it's worth checking your output, so you might want to do something similar to the below to mitigate:

select case when instr(lower('my_string'),'?') > 0
then nls_lower('my string', 'NLS_SORT = ARABIC')
else lower('my_string')
from dual

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" />

Order by case insensitive in oracle

First of all, you can order by the UPPER (or LOWER) case of the column, but once you've done that, you then need to sort by the text itself to get the order on the initial letter; eg:

with sample_data as (select 'A' txt from dual union all
select 'B' txt from dual union all
select 'Y' txt from dual union all
select 'Z' txt from dual union all
select 'a' txt from dual union all
select 'b' txt from dual union all
select 'y' txt from dual union all
select 'z' txt from dual)
select txt
from sample_data
order by upper(txt) desc, txt;

TXT
---
Z
z
Y
y
B
b
A
a

SQL- Ignore case while searching for a string

Use something like this -

SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER('%PriceOrder%')

or

SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER('%PriceOrder%')


Related Topics



Leave a reply



Submit