Re: Using LIKE with a LONG field

Hi Kevin,

You can't use like with LONG field. But if you can use LOB type instead of LONG you can use DBMS_LOB package. It has some procedures to compare LOBs.

For example:

You can create sql statement which works like 'LIKE':

create table tmp_lob (
  id number not null,
  big_text clob
);

insert into tmp_lob values (1, 'Long long text');

select id
from tmp_lob
where dbms_lob.instr(big_text, 'long') > 0;

Regards,
Michael
Brainbench MVP for Oracle Programming
http://www.brainbench.com

kevin_at_questionmark.com (Kevin Blount) wrote in message news:<1511a6a5.0208201240.9819194_at_posting.google.com>...
> I'm trying to search through a LONG field which contains the
> description of an item in my database. However, when I use the
> following SQL Query I am told that I have 'inconsistant datatypes'
> when I run the Query (in ASP)
>
> SELECT * FROM resources_objects WHERE resource_object_description
> LIKE '%string%'
>
> Any idea how I can use LIKE with a LONG field? I'm pretty certain it's
> this LONG field type that's caussing the issue, as the same SQL Query
> with a different field type (VARCHAR2) works fine.
Received on Fri Aug 23 2002 - 09:23:49 CDT