Re: Oracle Text, wildcards AND escape characters help

Michael O'Shea wrote:
> 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>
Ok, what if i want to searc for the word "fuzzy", or a contract number that has "-" in it?

SQL> select count(projectid) from project   2 where contains(contract_number, '(F30602-03-2%)',1)>0;

COUNT(PROJECTID)

             876

SQL> select count(projectid) from project   2 where contains(contract_number, '(F30602-03-2)', 1) >0;

COUNT(PROJECTID)

             877

SQL> select count(projectid) from project   2 where contains(contract_number, '{F30602-03-2}', 1) >0;

COUNT(PROJECTID)

              23

I could in the front end of the application have a bunch of replace statements, for whenever there is a reverse word, place "{}" around it, but that seems ill logical. The best way i can see to do it is to encapsulate the search string in the "{}", but when it reaches the "%", perform a wildcard action. example..

select count(projectid) from project
where contains(contract_number, '{agent(%)}', 1) >0;

this statment doesn't work, but I need something along those lines.

Thank you,
Jimi Received on Wed Sep 21 2005 - 12:27:50 CDT