Re: Search using like in a clob column

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Tue, 31 Aug 2010 17:56:20 +0200
Message-ID: <AANLkTik1Sent_yQcZcrKgUzaGu271pD5RoEtk=gWf_uz_at_mail.gmail.com>

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_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Tue, Aug 31, 2010 at 11:19 AM, Octavian Rasnita <orasnita_at_gmail.com>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_at_oracle-base.com>
> *To:* gus.spier_at_gmail.com
> *Cc:* orasnita_at_gmail.com ; oracle-l_at_freelists.org
> *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_at_gmail.com> 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_at_gmail.com>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
>>>
>>> --
>>> http://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
>

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

Received on Tue Aug 31 2010 - 10:56:20 CDT