SQL & PL/SQL

Hi

I have a program supporting several databases. F.ex SQL2000 and Oracle 8/9.

The field in question, text, might contain a lot of characters. In Oracle, it's a CLOB,
on SQL2000 it's a varchar. It is not possible for me to alter the database-structure in
any ways at this moment. The database-schema is locked.

My question is pretty simple, you have probably seen it before.

In SQL2000 I can use this query:

select count(*) from crm5.text where text like 'n%'.

The result returned = 8.

The same on Oracle will be somewhat like:

select count(*) from crm5.text where dbms_lob.instr( text, 'n') > 0

The result returned = 29.

The databases has the same data.

It seems to me that the dbms_lob.instr
does a (case-sensitive) query like this:

select count(*) from crm5.text where text like '%n%

Is this the right assumption? How can I get the correct number
of rows and do the search case-insensitive at the same time not
killing the performance of the database? We use a pretty smart
little thing on "normal" LIKE. But how can we do this with the
CLOB?

Regards,



Ivar