Re: Oracle Text, wildcards AND escape characters help

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 21 Sep 2005 06:00:41 -0700
Message-ID: <1127307641.829130.16440@g44g2000cwa.googlegroups.com>

Hi, you should be using () and not {}, at least for the specific requirements stated so far.

Example script below.

Regards

TESSELLA Michael.OShea_at_tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429

SQL>
SQL> CREATE TABLE tblTest(id NUMBER,testContent VARCHAR2(50));

Table created.

SQL> INSERT INTO tblTest(id,testContent) VALUES (1,'agents');

1 row created.

SQL> INSERT INTO tblTest(id,testContent) VALUES (2,'agent');

1 row created.

SQL> INSERT INTO tblTest(id,testContent) VALUES (3,'this is a line with agent in it');

1 row created.

SQL> INSERT INTO tblTest(id,testContent) VALUES (4,'this is a line with agentstem in it');

1 row created.

SQL> INSERT INTO tblTest(id,testContent) VALUES (5,'xxxagent xxxxx');

1 row created.

SQL>
SQL> CREATE INDEX idxtblTest
  2 ON tblTest(testContent)
  3 INDEXTYPE IS CTXSYS.CONTEXT; Index created.

SQL>
SQL> --Use of ()
SQL> SELECT id,testContent

  2 FROM tblTest
  3 WHERE CONTAINS(testContent,'(agent%)',1)>0;

ID TESTCONTENT

-- --------------------------------------------------

 1 agents
 2 agent
 3 this is a line with agent in it
 4 this is a line with agentstem in it

SQL>
SQL> --use of {}
SQL> SELECT id,testContent

  2 FROM tblTest
  3 WHERE CONTAINS(testContent,'{agent%}',1)>0;

ID TESTCONTENT

-- --------------------------------------------------

 2 agent
 3 this is a line with agent in it

SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Solaris: Version 10.1.0.3.0 - Production NLSRTL Version 10.1.0.3.0 - Production

SQL>
SQL> Received on Wed Sep 21 2005 - 08:00:41 CDT