Look into using regexp_like instead of like, along with the case-insensitive operator "i". It's well documented in the sql reference. Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Tue, Aug 31, 2010 at 11:19 AM, Octavian Rasnita <orasnita@xxxxxxxxx>wrote: > Thank you. I already read about Oracle Text, but The following part has a > non-standard syntax: > > WHERE CONTAINS(doc, 'SQL Server', 1) > 0 > > because I don't know how to use it in DBIx::Class ORM. > > It would have been much easier if it was > > where doc CONTAINS 'SQL Server'; > > In the program I make, it was very easy to port most of the database > queries from MySQL to Oracle because I use this ORM, but now I find some > problems with the text fields because they can't be accessed as easy as the > varchar2 fields. > > -- > Octavian > > ----- Original Message ----- > *From:* Tim Hall <tim@xxxxxxxxxxxxxxx> > *To:* gus.spier@xxxxxxxxx > *Cc:* orasnita@xxxxxxxxx ; oracle-l@xxxxxxxxxxxxx > *Sent:* Tuesday, August 31, 2010 11:37 AM > *Subject:* Re: Search using like in a clob column > > Hi. > > Oracle Text allows you to index VARCHAR2s and CLOBs and the CONTAINS and > CATSEARCH operators allow you to perform the equivalent of LIKE efficiently > on them. > > > http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php > > Remember, added more and complex indexes to a table increases the overhead > on DML, so they are not a universal cure-all. > > Cheers > > Tim... > > > On Tue, Aug 31, 2010 at 4:10 AM, Gus Spier <gus.spier@xxxxxxxxx> wrote: > >> 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 >> >> On Mon, Aug 30, 2010 at 12:56 PM, Octavian Rasnita <orasnita@xxxxxxxxx>wrote: >> >>> 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 >>> >>> -- >>> //www.freelists.org/webpage/oracle-l >>> >>> >>> >> > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5410 (20100830) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 5411 (20100831) __________ > > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com >