OraFAQ Forum: Text & interMedia » How to create refine catsearch

Home » Server Options » Text & interMedia » How to create refine catsearch

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to create refine catsearch [message #232309]

Fri, 20 April 2007 01:53

thief
Messages: 5
Registered: April 2007

Junior Member

HI guys n gals...
I have a problem....Well...i have data like

Item
------------
1) Barbara"s
2) Barbaras
3) Barbara"s Co

So i use catsearch in order to get a data...

the code i use is

Select * from grocery where catsearch(item,'&x*',null)>0;
This code will prompt for user input.

So when i enter barbara....the result are all 3 data is shown.

But when i type Barbaras....i only get 1 data..

How to make the " to be ingnored??or any other character to be ignored such as . , / +

Please help me

Report message to a moderator

Re: How to create refine catsearch [message #232473 is a reply to message #232309]

Fri, 20 April 2007 12:12

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Try:

Select * from grocery
where catsearch(REPLACE(REPLACE(item,'"',''),'''',''),'&x*',null)>0;

or:

Select * from grocery
where REPLACE(REPLACE(item,'"',''),'''','') LIKE '&x' || '%';

You can use additional REPLACEs to remove any unwanted char.

You may also like to check TRANSLATE function to to it at once.

HTH.
Michael

[Updated on: Fri, 20 April 2007 12:13]

Report message to a moderator

Re: How to create refine catsearch [message #232648 is a reply to message #232473]

Sun, 22 April 2007 19:51

thief
Messages: 5
Registered: April 2007

Junior Member

HI michael,

I tried the code that you gave...But it doesnt work....

It says that catsearch does not support functional invocation

[Updated on: Sun, 22 April 2007 20:08]

Report message to a moderator

Re: How to create refine catsearch [message #232649 is a reply to message #232309]

Sun, 22 April 2007 19:59

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>But it doesnt work....
While the statement above may be true, it totally useless to any person other than OP.

Question - what part of your question involves PL/SQL or requires an "Expert" to correctly respond.

PLEASE read & follow the #1 STICKY post at the top of this forum.

Report message to a moderator

Re: How to create refine catsearch [message #232666 is a reply to message #232473]

Sun, 22 April 2007 21:34

thief
Messages: 5
Registered: April 2007

Junior Member

Hi michael...
I tried the translate function aso...it seems it just translate for the view part...
but when i query for barbaras....i only get 1 output.
I still cant get all 3 output.

Report message to a moderator

Re: How to create refine catsearch [message #232909 is a reply to message #232473]

Tue, 24 April 2007 00:22

thief
Messages: 5
Registered: April 2007

Junior Member

Hi michael...
I tried the translate function aso...it seems it just translate for the view part...
but when i query for barbaras....i only get 1 output.
I still cant get all 3 output.

Report message to a moderator

Re: How to create refine catsearch [message #232930 is a reply to message #232909]

Tue, 24 April 2007 02:31

Maaher
Messages: 7065
Registered: December 2001

Senior Member

CTXCAT is limited in its use. I think you should be looking for a CONTEXT index. That allows a fuzzy search:

CREATE TABLE yourtable(thetext VARCHAR2(20))
/

INSERT INTO yourtable VALUES ('Barbara"s')
/
INSERT INTO yourtable VALUES ('Barbaras')
/
INSERT INTO yourtable VALUES('Barbara"s Co')
/

CREATE INDEX yourindex ON yourtable(thetext) INDEXTYPE IS CTXSYS.CONTEXT
/

SELECT * 
FROM   yourtable
WHERE  CONTAINS(thetext,'Fuzzy(Barbaras)')>0
/

DROP TABLE yourtable
/

MHE

Report message to a moderator

Re: How to create refine catsearch [message #232968 is a reply to message #232930]

Tue, 24 April 2007 05:24

thief
Messages: 5
Registered: April 2007

Junior Member

Ok michael..i will try

Report message to a moderator

Re: How to create refine catsearch [message #233116 is a reply to message #232968]

Tue, 24 April 2007 15:38

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

A ctxcat index with a catsearch query is usually more efficient than a context index with a contains query. You can use a query template with a catsearch query in order to use the context grammar, so that more of the context features are available, such as fuzzy searching. However, a better solution to your problem is to specify the special characters as skipjoins, so that they are ignored in indexing and searching. Please see the demonstration below, where I have first reproduced the problem, then shown the query template with the context grammar, then the better solution with the skipjoins. I will move this to the text forum. In the future, please post Oracle Text questions in the text forum.

-- test data:

SCOTT@10gXE> CREATE TABLE grocery (item VARCHAR2 (20))
  2  /

Table created.

SCOTT@10gXE> INSERT ALL
  2  INTO grocery VALUES ('Barbara''s')
  3  INTO grocery VALUES ('Barbara"s')
  4  INTO grocery VALUES ('Barbaras')
  5  INTO grocery VALUES ('Barbara''s Co')
  6  INTO grocery VALUES ('Barbara"s Co')
  7  INTO grocery VALUES ('Barbara.s')
  8  INTO grocery VALUES ('Barbara,s')
  9  INTO grocery VALUES ('Barbara/s')
 10  INTO grocery VALUES ('Barbara+s')
 11  SELECT * FROM DUAL
 12  /

9 rows created.

SCOTT@10gXE> CREATE INDEX your_index ON grocery (item)
  2  INDEXTYPE IS CTXSYS.CTXCAT
  3  /

Index created.

-- reproduction of problem:

SCOTT@10gXE> SELECT * FROM grocery
  2  WHERE  CATSEARCH (item, '&x*', NULL) > 0
  3  /
Enter value for x: barbaras
old   2: WHERE  CATSEARCH (item, '&x*', NULL) > 0
new   2: WHERE  CATSEARCH (item, 'barbaras*', NULL) > 0

ITEM
--------------------
Barbaras

-- You can use a query template,
-- so you can use the context grammar
-- with a ctxcat index and catsearch,
-- instead of a context index with contains:

SCOTT@10gXE> SELECT * FROM grocery
  2  WHERE  CATSEARCH
  3  	      (item,
  4  	       '<query>
  5  		  <textquery grammar="context">?&x</textquery>
  6  		</query>',
  7  	       NULL) > 0
  8  /
Enter value for x: barbaras
old   5:              <textquery grammar="context">?&x</textquery>
new   5:              <textquery grammar="context">?barbaras</textquery>

ITEM
--------------------
Barbara's
Barbara"s
Barbaras
Barbara's Co
Barbara"s Co
Barbara.s
Barbara,s
Barbara/s
Barbara+s

9 rows selected.

-- A better solution is to make the
-- apostrophe and other characters skipjoins,
-- so that they are ignored when indexing and searching:

SCOTT@10gXE> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_lexer', 'BASIC_LEXER');
  3    CTX_DDL.SET_ATTRIBUTE	 ('your_lexer', 'SKIPJOINS'   , ',''."+/');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@10gXE> DROP INDEX your_index
  2  /

Index dropped.

SCOTT@10gXE> CREATE INDEX your_index ON grocery (item)
  2  INDEXTYPE IS CTXSYS.CTXCAT
  3  PARAMETERS ('LEXER your_lexer')
  4  /

Index created.

SCOTT@10gXE> SELECT * FROM grocery
  2  WHERE  CATSEARCH (item, '&x*', NULL) > 0
  3  /
Enter value for x: barbaras
old   2: WHERE  CATSEARCH (item, '&x*', NULL) > 0
new   2: WHERE  CATSEARCH (item, 'barbaras*', NULL) > 0

ITEM
--------------------
Barbara's
Barbara"s
Barbaras
Barbara's Co
Barbara"s Co
Barbara.s
Barbara,s
Barbara/s
Barbara+s

9 rows selected.

Report message to a moderator

Previous Topic:	Ctx_doc.ifilter problem
Next Topic:	Multi-column index setup question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 10:50:44 CST 2024