?
Solved

Oracle: DBMS_LOB.INSTR - Searching for a pattern

Posted on 2003-03-28
8
Medium Priority
?
10,669 Views
Last Modified: 2007-12-19
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
Comment
Question by:soniva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 2

Expert Comment

by:ypwitkow
ID: 8224028
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
 

Author Comment

by:soniva
ID: 8224084
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
 

Author Comment

by:soniva
ID: 8224106
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:soniva
ID: 8224132
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
 
LVL 2

Expert Comment

by:ypwitkow
ID: 8224153
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
 
LVL 2

Accepted Solution

by:
ypwitkow earned 300 total points
ID: 8224170
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
 

Author Comment

by:soniva
ID: 8224176
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
 

Author Comment

by:soniva
ID: 8224182
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question