CODEWITHSUNDEEP
sqloracle-databaseindexingclob
user897237
user897237

Reputation: 623

Case insensitive search on clob fields

I'm trying to perform case insensitive search on clob fields. I don't want to use neither upper nor regexp_like. Alter session is also not an option for me. I have tried to put an index like this:

create index
   ind_ci
on
   table
(
   nlssort( column, 'NLS_SORT=BINARY_CI')
);

It seems however to fail on clob fields. How can I reach the goal using index?

Upvotes: 1

Views: 1430

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Unfortunately, NLSSORT function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

You could have a look at how implicit data conversion happens.

Also, you could read Oracle – Case Insensitive Sorts & Compares.

Upvotes: 1

Related Questions