Solved

Oracle8 - LIKE OPERATOR ON CLOB

Posted on 2003-12-06
4
3,826 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
Comment Utility
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
Comment Utility
That helped a lot. Could you make it case-INsensitive too?
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 250 total points
Comment Utility
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
Comment Utility
Off course :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
levels for reporting 5 48
How to count the number of rows in multiple Oracle Tables 10 42
Oracle DATE Column Space 11 41
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now