0

Seems like Oracle comparison operators = and like are case-sensitive
Without changing the NLS_COMP: Is it possible to add a hint per query to make them case-insensitive?

something like:    select /*IGNORE_CASE*/ from tab where val like '%noMatTer%'

Is it possible introduce/"teach"/code a new hint in the Oracle DB?
Anything on the query level can be done? (besides the obvious: WHERE upper(user_name) LIKE '%ME%')

JavaSheriff
  • 7,074
  • 20
  • 89
  • 159
  • 1
    Possible duplicate of [Case insensitive searching in Oracle](http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle) – EvilTeach Mar 17 '17 at 20:11

2 Answers2

4

There is no hint. However, from the new features guide in 12c...

Case-Insensitive Database Oracle Database supports case-insensitive collations, such as BINARY_CI or GENERIC_M_CI. By applying such collations to SQL operations, an application can perform string comparisons and matching in a case-insensitive way, independent of the language of the data. With the new Oracle Database 12c Release 2 (12.2) ability to declare collations for columns, you can declare a column to always be compared in a case-insensitive way. The column collation, if not specified explicitly, is inherited from a table default collation, which in turn is inherited from a schema default collation. This way, you can easily declare all character columns of an application in a database as case-insensitive.

With this feature, developers can declare data as case-insensitive and do not have to add explicit uppercasing operations to SQL statements. This feature simplifies application migration to Oracle Database from non-Oracle Database systems that allow such declarations.

BobC
  • 4,208
  • 1
  • 12
  • 15
0

Did you consider using lower() function for the query-level remedy?

select * from tab where lower(val) like lower('%noMatTer%')
B Samedi
  • 380
  • 3
  • 11