Re: LIKE help

thanks alot.
this looks to be exactly what i need.
thanks again.
-maurice
samuels_at_seas.upenn.edu

Douglas Hawthorne (douglashawthorne_at_yahoo.com.au) wrote:
: <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 - 12:20:10 CST