Home » Server Options » Text & interMedia » full text search in oracle (merged 2)
full text search in oracle (merged 2) [message #547632] Thu, 15 March 2012 04:31 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello friends,
i have a problem in full text searching in my database.
please guide me how should i find the all correspondind records.
and scenario is like--

suppose i have a table called "msg" and it has one field named "text" and it has vaules-------

this is the test;
test successfull;
test completed
test pending
pending
hello
hi
no pending
test pending


then when i give any string in search condition then i fetch all records in which a the any part of search string exist.
like-- i give 'test pending' then it fetch all records which has either 'test' or 'pending' or both.

thanx in advance.
Re: full text search in oracle (merged 2) [message #547678 is a reply to message #547632] Thu, 15 March 2012 12:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> create table msg
  2    (text  varchar2(60))
  3  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into msg values ('this is the test;')
  3  into msg values ('test successfull;')
  4  into msg values ('test completed')
  5  into msg values ('test pending')
  6  into msg values ('pending')
  7  into msg values ('hello')
  8  into msg values ('hi')
  9  into msg values ('no pending')
 10  into msg values ('test pending')
 11  select * from dual
 12  /

9 rows created.

SCOTT@orcl_11gR2> create index msg_text_idx on msg (text)
  2  indextype is ctxsys.context
  3  /

Index created.

SCOTT@orcl_11gR2> variable search_string varchar2(60)
SCOTT@orcl_11gR2> exec :search_string := 'test pending'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select * from msg
  2  where  contains
  3  	      (text,
  4  		 replace (:search_string, ' ', ' or ')) > 0
  5  /

TEXT
------------------------------------------------------------
this is the test;
test successfull;
test completed
test pending
pending
no pending
test pending

7 rows selected.

SCOTT@orcl_11gR2>

Re: full text search in oracle (merged 2) [message #547743 is a reply to message #547678] Fri, 16 March 2012 03:04 Go to previous message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thank you very much mam for your effort in solving my problem, its working fine.
thanx once again for your explanation in a very easier way thanx a lot.
Previous Topic: Fulltext functional lookup on BLOB
Next Topic: PLS-00201: identifier 'CTX_THES' must be declared, while creating thesaurus
Goto Forum:
  


Current Time: Fri Mar 29 09:27:13 CDT 2024