Home » Server Options » Text & interMedia » Text searching
Text searching [message #189181] Wed, 23 August 2006 09:31 Go to next message
SaraC
Messages: 81
Registered: August 2005
Member
Hi

I want to search a concatenation of columns for string tokens - trying to implement a free text search feature.

Is there any other way than to use WHERE concat_string LIKE '%AB%' etc could be that I have any number of tokens to search the string for and not (necessarily in the order they appear in the concatenated string (if they appear)

Will keep looking myself and check back to see if anyone has answered. Working with 9i.

Thanks

[Updated on: Wed, 23 August 2006 09:59]

Report message to a moderator

Re: Text searching [message #189216 is a reply to message #189181] Wed, 23 August 2006 11:51 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Take a look at Oracle Text
Re: Text searching [message #189349 is a reply to message #189181] Thu, 24 August 2006 04:26 Go to previous messageGo to next message
SaraC
Messages: 81
Registered: August 2005
Member
Thanks frank that looks useful. Have you used it. Does it only support searching for whole words not partial strings

i.e. like '%hi% would match high but using oracle Text searching doesnt seem to...

Actually I got CONTAINS to return using wildcard but not CATSEARCH.

however read that contains needs the index to be synchronised after each DML statement and as there could be numerous updates to my table at any time the catsearch might be better since it doesnt need the synchronising but doesnt offer the wildcard.

Maybe it is better to just use LIkE %% for my case as I think its relatively small compared to how these features are supposed to be used i.e search from documents etc rather than just table values.

S

[Updated on: Thu, 24 August 2006 05:38]

Report message to a moderator

Re: Text searching [message #189358 is a reply to message #189349] Thu, 24 August 2006 04:44 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I think it would. Look at wildcard operators
And no, I never used it myself, so I will not be able to help you in-depth, but I am sure that others will.
Re: Text searching [message #189366 is a reply to message #189358] Thu, 24 August 2006 04:56 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Try this:

SELECT SCORE(1), title 
FROM my_text_table
WHERE CONTAINS(textcol, '%hi%', 1) > 0;


Example:

SQL> CREATE TABLE my_text_table(
  2    id NUMBER PRIMARY KEY,
  3    title VARCHAR2(30),
  4    doc CLOB
  5  );

Table created.

SQL>
SQL> INSERT INTO my_text_table VALUES(1, 'doc1', 'Hi Sara, ...');

1 row created.

SQL> INSERT INTO my_text_table VALUES(2, 'doc2', 'Hi Hi Mam');

1 row created.

SQL> INSERT INTO my_text_table VALUES(3, 'doc3', 'A highly recommended document');

1 row created.

SQL> INSERT INTO my_text_table VALUES(4, 'doc4', 'Another document');

1 row created.

SQL>
SQL> CREATE INDEX my_text_index ON my_text_table(doc)
  2    INDEXTYPE IS ctxsys.context;

Index created.

SQL>
SQL> SELECT SCORE(1), title
  2    FROM my_text_table
  3   WHERE CONTAINS(doc, '%hi%', 1) > 0;

  SCORE(1) TITLE
---------- ------------------------------
         4 doc1
         8 doc2
         4 doc3
Re: Text searching [message #189407 is a reply to message #189181] Thu, 24 August 2006 08:47 Go to previous messageGo to next message
SaraC
Messages: 81
Registered: August 2005
Member
Thanks Frank didnt realise the topic had new replies - I edited my first post after I tried out the contains - instead of posting a new one Smile
Re: Text searching [message #189478 is a reply to message #189349] Thu, 24 August 2006 14:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
If you are using a context index with a contains query, then the wildcard is %. If you are using a ctxcat index with a catsearch query, then the wildcard is *.

If you are using Oracle 10g and you want to automatically synchronize your context index, you can use sync (on commit) like so:

CREATE INDEX your_index ON your_table (your_column)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC (ON COMMIT)');

If using Oracle 9i, you can automatically synchronize your index using an after insert or update trigger that uses dbms_job.submit to run ctx_ddl.sync_index upon commit.

If you are searching small strings, you may get better performance out of like or instr. Oracle text is designed for searching large documents. If ctxcat and catsearch have all of the options that you need, then that is usually more efficient than context and contains.
icon7.gif  Re: Text searching [message #436962 is a reply to message #189478] Tue, 29 December 2009 23:46 Go to previous messageGo to next message
ora22
Messages: 8
Registered: December 2009
Location: BOSTON
Junior Member
excellent summary ..

i have one follow-up question

in my current situation ctxcat type index seem to be a perfect fit because of

- small text fragments to be searched
- simple seraches without things like thesaurus etc etc
- mixed searches where text seraches have to be combined with other columns

HOWEVER

i do need to be able to execute the text serach on multiple columns. in the case of context type indexes that is doable by creating multi_column_datastore and section groups and then using WITHIN in the CONTAINS ... as described in the thread :

www.orafaq.com/forum/t/88599/0/

MY Question is - if using ctxcat - is there a way to search on multiple coulumns with a WITHIN type functionality ?

Will appreciate your reply.
Re: Text searching [message #436997 is a reply to message #436962] Wed, 30 December 2009 04:39 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
ora22 wrote on Tue, 29 December 2009 21:46

if using ctxcat - is there a way to search on multiple coulumns with a WITHIN type functionality ?


No. CTXCAT is limited to one text column. You could create a concatenated column or a view that concatenates the columns and create your index on that, in order to be able to search multiple columns, but you would not be able to specify which colunn using WITHIN or any other method. You will need a CONTEXT index.
Previous Topic: bad format when converting RTF 2 Plain text (merged)
Next Topic: CONTAINS function not working! (split from hijacked thread by bb)
Goto Forum:
  


Current Time: Thu Mar 28 10:58:50 CDT 2024