Re: Search using like in a clob column
- From: "Octavian Rasnita" <orasnita@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Aug 2010 11:33:40 +0300
Hi Gus,
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@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
Other related posts:
