Other Oracle database topics - Oracle Text, wildcards AND escape characters help

Author

Oracle Text, wildcards AND escape characters help

jimi_xyz@hotmail.com

2005-09-19, 1:24 pm

Hi,
Here is what im trying to do, the SQL statement looks like this..

select count(projectid) from project
where contains(objectives,
'agent', 1) >0;

COUNT(PROJECTID)
----------------
70

~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~
select count(projectid) from project
where contains(objectives,
'agent%', 1) >0;

COUNT(PROJECTID)
----------------
90

~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~
~~

Now this is what im looking for...

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

This SQL statement works, but it escapes the wildcard; my question is
how do I make the wildcard valid? If this statement actually used the
wildcard it should return a count of 90, but it only returns a count of
70. Any ideas or help will be apprieciated.

Thank you,
jimi

Pratap

2005-09-20, 1:23 pm

where contains(objectives,
'{agent}%', 1) >0;

jimi_xyz@hotmail.com

2005-09-20, 1:23 pm

That doesn't work...

SQL> select count(projectid) from project
2 where contains(objectives,
'{agent}%', 1) >0;
select count(projectid) from project
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

Michael O'Shea

2005-09-21, 9:23 am

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

Example script below.

Regards

TESSELLA Michael.OShea@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,testConte
nt) VALUES (1,'agents');

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SQL> INSERT INTO tblTest(id,testConte
nt) 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>

jimi_xyz@hotmail.com

2005-09-21, 1:23 pm

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@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,testConte
nt) VALUES (1,'agents');
>
> 1 row created.
>
> SQL> INSERT INTO tblTest(id,testConte
nt) VALUES (2,'agent');
>
> 1 row created.
>
> SQL> INSERT INTO tblTest(id,testConte
nt) VALUES (3,'this is a line with
> agent in it');
>
> 1 row created.
>
> SQL> INSERT INTO tblTest(id,testConte
nt) VALUES (4,'this is a line with
> agentstem in it');
>
> 1 row created.
>
> SQL> INSERT INTO tblTest(id,testConte
nt) 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_nu
mber, '(F30602-03-2%)',1)>0;

COUNT(PROJECTID)
----------------
876

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

COUNT(PROJECTID)
----------------
877

SQL> select count(projectid) from project
2 where contains(contract_nu
mber, '{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_nu
mber, '{agent(%)}', 1) >0;

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

Thank you,
Jimi

jimi_xyz@hotmail.com

2005-09-21, 1:23 pm

Barbara Boehmer

2005-09-21, 8:23 pm

Perhaps something like this?:

scott@ORA92> create table project
2 (projectid number,
3 contract_number varchar2(60))
4 /

Table created.

scott@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@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@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@ORA92> create index project_idx
2 on project (contract_number)
3 indextype is ctxsys.context
4 parameters ('lexer my_lexer')
5 /

Index created.

scott@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@ORA92> variable terms varchar2(60)
scott@ORA92> exec :terms := 'F30602-03-2'

PL/SQL procedure successfully completed.

scott@ORA92> exec :terms := replace (:terms, '-', '\-')

PL/SQL procedure successfully completed.

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

PROJECTID CONTRACT_NUMBER
---------- ------------------------------------------------------------
1 F30602-03-2

scott@ORA92> exec :terms := 'F30602-03-2%'

PL/SQL procedure successfully completed.

scott@ORA92> exec :terms := replace (:terms, '-', '\-')

PL/SQL procedure successfully completed.

scott@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@ORA92>

jimi_xyz@hotmail.com

2005-09-21, 8:23 pm

Barbara,
That might work I'll give that a try tomarrow.
Thank you,
Jimi

Barbara Boehmer wrote:
> Perhaps something like this?:
>
> scott@ORA92> create table project
> 2 (projectid number,
> 3 contract_number varchar2(60))
> 4 /
>
> Table created.
>
> scott@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@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@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@ORA92> create index project_idx
> 2 on project (contract_number)
> 3 indextype is ctxsys.context
> 4 parameters ('lexer my_lexer')
> 5 /
>
> Index created.
>
> scott@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@ORA92> variable terms varchar2(60)
> scott@ORA92> exec :terms := 'F30602-03-2'
>
> PL/SQL procedure successfully completed.
>
> scott@ORA92> exec :terms := replace (:terms, '-', '\-')
>
> PL/SQL procedure successfully completed.
>
> scott@ORA92> select * from project
> 2 where contains (contract_number, :terms, 1) >0
> 3 /
>
> PROJECTID CONTRACT_NUMBER
> ---------- ------------------------------------------------------------
> 1 F30602-03-2
>
> scott@ORA92> exec :terms := 'F30602-03-2%'
>
> PL/SQL procedure successfully completed.
>
> scott@ORA92> exec :terms := replace (:terms, '-', '\-')
>
> PL/SQL procedure successfully completed.
>
> scott@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@ORA92>

Sponsored Links