Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle8 - LIKE OPERATOR ON CLOB

Posted on 2003-12-06
4
Medium Priority
?
3,927 Views
Last Modified: 2008-02-01
In Oracle 9 I could use the LIKE operator on CLOB objects, but it doesn't seems to work in Oracle 8.

Is there an alternative way to do it in Oracle 8 ?

example: select * from article where bodyText LIKE '%TEST%'

Batalf
0
Comment
Question by:Batalf
  • 2
  • 2
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9888420
that's right, in Oracle8i you have to use dbms_lob package.


SQL> SELECT * FROM A WHERE dbms_lob.instr(<column>,'<search_string>',1,1)>0;

hope this helps




0
 
LVL 32

Author Comment

by:Batalf
ID: 9888436
That helped a lot. Could you make it case-INsensitive too?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 1000 total points
ID: 9888472
I guess you can do it by adding another layer of function like upper() or lower() like this:

SELECT * FROM A WHERE dbms_lob.instr(upper(<column>),upper('<search_string>'),1,1)>0;
0
 
LVL 32

Author Comment

by:Batalf
ID: 9888495
Off course :-)
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

876 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