Re: Oracle Text, wildcards AND escape characters help

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 21 Sep 2005 12:40:13 -0700
Message-ID: <1127331613.301294.298840@f14g2000cwb.googlegroups.com>

Perhaps something like this?:

scott_at_ORA92> create table project

  2    (projectid	number,
  3  	contract_number varchar2(60))

  4 /

Table created.

scott_at_ORA92> insert all

  2  into project values (1, 'F30602-03-2')
  3  into project values (2, 'F30602-03-2-1')
  4  into project values (2, 'F30602-03-2-2')
  5  into project values (2, 'F30602-03-2-3')

  6 select * from dual
  7 /

4 rows created.

scott_at_ORA92> select * from project
  2 /

 PROJECTID CONTRACT_NUMBER
---------- ------------------------------------------------------------

         1 F30602-03-2
         2 F30602-03-2-1
         2 F30602-03-2-2
         2 F30602-03-2-3

scott_at_ORA92> begin
  2 ctx_ddl.create_preference ('my_lexer', 'basic_lexer');   3 ctx_ddl.set_attribute ('my_lexer', 'printjoins', '-');   4 end;
  5 /

PL/SQL procedure successfully completed.

scott_at_ORA92> create index project_idx
  2 on project (contract_number)
  3 indextype is ctxsys.context
  4 parameters ('lexer my_lexer')
  5 /

Index created.

scott_at_ORA92> select token_text from dr$project_idx$i   2 /

TOKEN_TEXT

---

F30602-03-2
F30602-03-2-1
F30602-03-2-2
F30602-03-2-3

scott_at_ORA92> variable terms varchar2(60) scott_at_ORA92> exec :terms := 'F30602-03-2'

PL/SQL procedure successfully completed.

scott_at_ORA92> exec :terms := replace (:terms, '-', '\-')

PL/SQL procedure successfully completed.

scott_at_ORA92> select * from project
  2 where contains (contract_number, :terms, 1) >0   3 /

 PROJECTID CONTRACT_NUMBER
---------- ------------------------------------------------------------

         1 F30602-03-2

scott_at_ORA92> exec :terms := 'F30602-03-2%'

PL/SQL procedure successfully completed.

scott_at_ORA92> exec :terms := replace (:terms, '-', '\-')

PL/SQL procedure successfully completed.

scott_at_ORA92> select * from project
  2 where contains (contract_number, :terms, 1) >0   3 /

 PROJECTID CONTRACT_NUMBER
---------- ------------------------------------------------------------

         2 F30602-03-2-3
         2 F30602-03-2-2
         2 F30602-03-2-1
         1 F30602-03-2

scott_at_ORA92> Received on Wed Sep 21 2005 - 14:40:13 CDT