CODEWITHSUNDEEP
sqloracle-database
Geek
Geek

Reputation: 3329

best practise for sql in clause with upper keyword

I have a scenario where my in clause should compare string value. For string i do an upper on the left and right of the in clause like below

select * from cases where upper(case_name) = upper(String);//value passed dynamically.

Now, this value is a multi-select field. So there may be more than 1 string. In that case what is the best approach to compare values.

select * from cases where upper(case_name) = upper('ABC');//value passed dynamically with 1 selection.

select * from cases where upper(case_name) = upper('ABC', 'BCD');//value passed dynamically with more than 1 selection.this query wont work but just showing the requirement.

Please let me know which would be the best approach. I have to do this in java with hibernate native query. So keeping that in mind please suggest. thank you.

Upvotes: 0

Views: 3022

Answers (2)

William Robertson
William Robertson

Reputation: 16001

From Oracle 12.2 you can use a case-insensitive collation:

select * from dual
where  dummy collate binary_ci in ('x', 'y', 'z')

binary_ci ignores case.

binary_ai also ignores accents.

See the Database Globalization Support Guide for details of collation options.

In 12.2, this syntax doesn't work when cursor_sharing is set to FORCE, and you have to include a /*+ cursor_sharing_exact */ hint. It seems not to need it in 19c, though it may have been fixed earlier.

You can in theory set this as the default for a column, but only if you also set max_string_size to EXTENDED:

create or replace view demo as
select dummy collate binary_ci as dummy_ci
from   dual; 

ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set

Since changing max_string_size is a system-wide setting that cannot be undone, with potential side effects, I doubt many sites are able to set default collation for columns.

Upvotes: 1

Conffusion
Conffusion

Reputation: 4475

If you can have any number of strings to search, you can use in and pass a list as parameter value. I don't think you can fix the upper() around every value in the list, so you nee to change the search strings before in java to uppercase (String.toUpperCase()) and pass it as a list of upper cased values to your statement

select * from cases where upper(case_name) in (:namelist)

Remark 1: check in java if namelist is not empty, otherwise you get an SQL error because in ()is not valid SQL.

Remark 2: this will not use an index defined on case_name. You should create an index on 'upper(case_name)' so the upper cased values are indexed. See Function based indexes

Upvotes: 2

Related Questions