Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12006
  • Last Modified:

Oracle: DBMS_LOB.INSTR - Searching for a pattern

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.
0
soniva
Asked:
soniva
  • 5
  • 3
1 Solution
 
ypwitkowCommented:
dbms_lob.instr(text,'n') does case sensitive query, but looks for any position of 'n'

You should use:
select count(*) from crm5.text
where dbms_lob.instr( text, 'n') = 1
as equivalent for:
select count(*) from crm5.text where text like 'n%'

BTW in Oracle9 you should use LIKE for CLOB columns.

For case unsensitive queries use lower/upper functions.

Best regards,
Lucy
0
 
sonivaAuthor Commented:
To my knowledge I cannot use LIKE on CLOB's in Oracle 9. The query

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

on Oracle 9 returns ora-00932. Inconsistent datatype.

Replacing the "> 0" with "= 1" returns empty on my database. That is not the result I'm looking for.

Lower/Upper lowers performance way to much in my world.
( I should have mentioned that I do not want to use UPPER in the query. Tell Oracle to use UPPER and Oracle jumps to tablescan before you know it. )


Ivar.

0
 
sonivaAuthor Commented:
To my knowledge I cannot use LIKE on CLOB's in Oracle 9. The query

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

on Oracle 9 returns ora-00932. Inconsistent datatype.

Replacing the "> 0" with "= 1" returns empty on my database. That is not the result I'm looking for.

Lower/Upper lowers performance way to much in my world.
( I should have mentioned that I do not want to use UPPER in the query. Tell Oracle to use UPPER and Oracle jumps to tablescan before you know it. )


Ivar.

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sonivaAuthor Commented:
To my knowledge I cannot use LIKE on CLOB's in Oracle 9. The query

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

on Oracle 9 returns ora-00932. Inconsistent datatype.

Replacing the "> 0" with "= 1" returns empty on my database. That is not the result I'm looking for.

Lower/Upper lowers performance way to much in my world.
( I should have mentioned that I do not want to use UPPER in the query. Tell Oracle to use UPPER and Oracle jumps to tablescan before you know it. )


Ivar.

0
 
ypwitkowCommented:
Not:
select count(text) from crm5.text
where text like 'n%'
BUT:
select count(*) from crm5.text
where text like 'n%'

To avoid full tablescan for UPPER/LOWER you can use functional indexes.

Lucy
0
 
ypwitkowCommented:
And when
select count(*) from crm5.text
where dbms_lob.instr( text, 'n') = 1

gives 0
you should query:

select count(*) from crm5.text
where dbms_lob.instr( text, 'N') = 1

HTH,
Lucy
0
 
sonivaAuthor Commented:
OK, sorry for the Oracle 9. My mistake. Works fine on 9. But I still can't get the correct values on Oracle 8.

And as I said, doing changes, ANY changes, on the database is not possible. That goes for adding indexes too. I could have solved a lot of problems of I could solve any problems doing so (6000 customers creates a bit of a deployment problem).

Ivar.

0
 
sonivaAuthor Commented:
Ah. This gave me the last point :) After checking the database I got the point. The strings all started out with an big n.

Thank god it's friday!



Ivar.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now