Re: LIKE help

<samuels_at_blue.seas.upenn.edu> wrote in message news:c3vfbi$91e9$1_at_netnews.upenn.edu...
> hi,
> i am wondering what is the best way to do the following.
> i need to retrieve data from a table for rows where 2 varchar columns
don't contain certain pieces of text, ie
>
> select value1 from test_table
> where (text_column1 not like '%CONDITION1%'
> and text_column1 not like '%CONDITION2%'
> and text_column1 not like '%CONDITION3%')
> or
> (text_column2 not like '%CONDITION1%'
> and text_column2 not like '%CONDITION1%'
> and text_column3 not like '%CONDITION1%');
>
> i'm using oracle8.1.7.
> i have about 20 different conditions and really would hate to have to have
an "and column not like..." clause for each of them.
> thanks in advance.
> -maurice
>
> samuels_at_seas.upenn.edu

Maurice,

Have you considered InterMedia Text? See Ch.17 "interMedia" of "Expert One-on-One Oracle" by Thomas Kyte (A-Press 2003) for an overview.

I set up the following test case which may or may not correspond to your situation. You can still use your %CONDITIONS% strings. See p.4-41 of "Oracle8i interMedia Text Reference" for a description of the wildcards.

SQL> create table maurice( col1 varchar2(40), col2 varchar2(40));

Table created.

SQL> insert into maurice select owner col1, object_name col2 from all_objects;

23592 rows created.

The following two statements do all the hard work in analyzing the text in the columns. See p.2-14 of "Oracle8i interMedia Text Reference" for a description of the CREATE INDEX commands used with interMedia Text.

SQL> create index maurice_idx1 on maurice(col1) indextype is CTXSYS.CONTEXT;

Index created.

SQL> create index maurice_idx2 on maurice(col2) indextype is CTXSYS.CONTEXT;

Index created.

Now I want to find the number of rows that do not contain the words 'SYS' or 'SYSTEM' in COL1. See p.2-12 of "Oracle8i interMedia Text Reference" for a description of the CONTAINS operator.

SQL> SELECT COUNT(*) FROM maurice WHERE CONTAINS(col1, 'SYS|SYSTEM') = 0;

  COUNT(*)

     13507

The equivalent query using LIKE is as follows (COL1 is a one word column):

SQL> SELECT COUNT(*) FROM maurice WHERE col1 NOT LIKE 'SYS%';

  COUNT(*)

     13507

You should use bind variables as the following code shows:

SQL> variable scan_str VARCHAR2(50)
SQL> exec :scan_str := 'SYS%'

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM maurice WHERE CONTAINS( col1, :scan_str ) = 0;

  COUNT(*)

     13507

So your query would look something like (before setting the bind variables): SELECT *
   FROM your_table
   WHERE CONTAINS( text_column1, :text_str_1) = 0

      AND CONTAINS( text_column2, :text_str_2) = 0 ;

Douglas Hawthorne Received on Fri Mar 26 2004 - 05:20:55 CST