Re: Search using like in a clob column

Sorry for not beeing clear enough. I use Oracle version 11 (not the Enterprise version).

Yes, please tell me if there is a way of doing this search using regular expressions instead of using the LIKE operator and I will try that way.

Thank you.

--
Octavian



> From: Gus Spier <gus.spier_at_xxxxxxxxx>

> Date: Mon, 30 Aug 2010 23:10:05 -0400

> 

> You don't mention what version of Oracle you're using.  I haven't had

> opportunity to play around with them, but are Regular Expressions an option

> available to you?

> 

> Regards,

> Gus




> Hello,

>

> I have tried to search in a clob column using the like operator, but I can

> see that it doesn't work as expected.

>

> Is there a way of using a longer kind of varchar2 column, say... one that

> contains up to 200 KB to be able to search using the like operator?

>

> I am asking this because I can see that the clob columns have too many

> limitations and nowadays 200 KB doesn't mean a too "long" object.

>

> I see that I can search in a clob column using the like operator, but not

> case insensitively and I also found this search pretty strange, because if I

> use sqlplus I can search using something like:

>

> select * from table_name where clob_column like '%text%';

>

> It works, as well as:

> select * from table_name where CLOB_COLUMN like '%text%';

>

> ...but when I use the DBIx::Class ORM I can use the second line only

> (although the table name and the column were created without using quotes

> for forcing a certain column name case).

>

> I used:

>

> alter session set NLS_COMP='LINGUISTIC';

> alter session set NLS_SORT='BINARY_AI';

>

> (I have also tried to set only case insesitively and not accent

> insensitively using BINARY_CI, but with no good effect).

>

> If I have the same text in a varchar2 column, I can do the case and accent

> insensitive search, but not in the clob column because it returns the

> records only if they contain the exact match in that clob column.

>

> The reason I hope there is a way of avoiding to use Oracle Text is that the

> syntax I need to use is too specific to Oracle and I don't even know how

> easy would be to use it with the ORM I use.

>

> Thanks.

>

> Octavian

>







__________ Information from ESET NOD32 Antivirus, version of virus signature database 5410 (20100830) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



--
http://www.freelists.org/webpage/oracle-l