CODEWITHSUNDEEP
oracle-database
JavaSheriff
JavaSheriff

Reputation: 7665

Oracle case-sensitive comparison operators

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%')

Upvotes: 0

Views: 728

Answers (2)

B Samedi
B Samedi

Reputation: 380

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

select * from tab where lower(val) like lower('%noMatTer%')

Upvotes: 0

BobC
BobC

Reputation: 4416

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.

Upvotes: 4

Related Questions