Oracle TEXT search question

Have a need for the TEXT search condition. After I indexed the column description in T1 with Oracle TEXT, I use the syntax "contains"

select count(1) from t1 where contains( lower( description), 'abcd', 1) >0;

However I got the error complaining  "lower( description)" is not indexed. I knew it was not for this, but how should I deal with this situation? If I do the following, it is fast, but it is not the way to form the sql.
select count(1) from t1 where contains(  description, 'abcd', 1) >0;
select count(1) from t1 where contains(  description, 'Abcd', 1) >0;
All combinations.

(for normal where clause:  select count(1) from t1 where lower( description) like 'abcd'; when the table is big, it is too slow)
Who is Participating?
johnsoneConnect With a Mentor Senior Oracle DBACommented:
The default behavior for a text index is case insensitive.  Unless you changed that when you created the index, there should be no need for the lower function.
HuaMinChenConnect With a Mentor Business AnalystCommented:
Try not to put lower below
select count(1) from t1 where contains( lower( description), 'abcd', 1) >0;

Open in new window

Try also instr to do the search.
jl66Author Commented:
In my original question, I already mentioned the query you gave, which caused ORA- error: lower( description) is not indexed.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

HuaMinChenBusiness AnalystCommented:
Is the speed fine after you've removed 'lower(...)' to your query?
jl66Author Commented:
I specifically made sure that I used the default one. the search result still showed the case sensitive. Thanks for info though.
jl66Author Commented:
slightwv (䄆 Netminder) Commented:
You closed this before I had time to post.

It is not case sensitive.  I've used it since somewhere around version 7 before it was called Text.

Please run this test case:
drop table tab1 purge;
create table tab1(col1 varchar2(20));

insert into tab1 values('HeLlO');
insert into tab1 values('hello');
insert into tab1 values('HELLO');

create index tab1_idx on tab1(col1) indextype is ctxsys.context;

select * from tab1 where contains(col1,'hello') > 0;
select * from tab1 where contains(col1,'HELLO') > 0;

Open in new window

slightwv (䄆 Netminder) Commented:
Any reason you accepted a post that isn't even valid syntax?
johnsoneSenior Oracle DBACommented:
Honestly, I haven't used it since Oracle 8, before it was renamed to Text.  I did check the documentation and the BASIC_LEXER defaults still to case insensitive.

The default for mixed_case on the lexer is NO.  The AUTO_LEXER, if you are using that one, inherits the mixed_case value from the BASIC_LEXER, so it is still there and defaults to NO.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.