Home » Server Options » Text & interMedia » How to create refine catsearch
How to create refine catsearch [message #232309] Fri, 20 April 2007 01:53 Go to next message
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
Re: How to create refine catsearch [message #232473 is a reply to message #232309] Fri, 20 April 2007 12:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: How to create refine catsearch [message #232666 is a reply to message #232473] Sun, 22 April 2007 21:34 Go to previous messageGo to next message
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.

Re: How to create refine catsearch [message #232909 is a reply to message #232473] Tue, 24 April 2007 00:22 Go to previous messageGo to next message
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.
Re: How to create refine catsearch [message #232930 is a reply to message #232909] Tue, 24 April 2007 02:31 Go to previous messageGo to next message
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
Re: How to create refine catsearch [message #232968 is a reply to message #232930] Tue, 24 April 2007 05:24 Go to previous messageGo to next message
thief
Messages: 5
Registered: April 2007
Junior Member
Ok michael..i will try
Re: How to create refine catsearch [message #233116 is a reply to message #232968] Tue, 24 April 2007 15:38 Go to previous message
Barbara Boehmer
Messages: 9077
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.








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


Current Time: Thu Mar 28 17:34:26 CDT 2024