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
Rollback a Committed Transaction
Ssis Connection Not Found in Package
How to Specify in Clause in a Dynamic Query Using a Variable
How to Calculate a Moving Average Using MySQL
Merging Intervals in One Pass in SQL
Strip Out Non-Numeric Characters in Select
Register Clr Function (Wcf Based) in SQL Server 2012
How to Mark Certain Nr of Rows in Table on Concurrent Access
Select Multiple Columns from a Table, But Group by One
Sql: Get Next Relative Day of Week. (Next Monday, Tuesday, Wed.....)
Recursive Stored Functions in MySQL
Difference Between Varchar(500) VS Varchar(Max) in SQL Server
SQL Server 2005 Recursive Query with Loops in Data - Is It Possible
Oracle Connect by Clause Equivalent in SQL Server
Generating Rows Based on Column Value