CODEWITHSUNDEEP
oracle-databasefull-text-search
Fra83
Fra83

Reputation: 521

Oracle Full Text how to search similar words

first off all I am newbie to Oracle Text so please bear with me. I think that my situation is quite simple to manage, because I have to make a search on two fields of the same table and these fields are quite small. After reading some articles, I think that CTXCAT index is enough for me, but i don't understand how to search parts of words; for example, if I search 'peak', I would like to be returned words like 'speak' also. Can you help me to understand this?

Upvotes: 0

Views: 936

Answers (1)

Shariar Imtiaz
Shariar Imtiaz

Reputation: 493

Please visit below link:

http://www.oracle.com/technetwork/testcontent/ctxcat-primer-090555.html

Some portion from link mentioned above are below.

I want to find all auction items which contains the words "toy" and "dog" but not the phrase "live animal":

SELECT item_id, item_desc
 FROM auction WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', null) > 0;

A few points to note:

  1. ANDed terms do not need the word AND or even an "&" operator. AND is assumed between any adjacent terms.
  2. NOT is represented by "|" (OR, not used here, is represented by "|").
  3. Parentheses can be used to group terms.
  4. Double quotes are used to surround a phrase (otherwise "live animal" would have been read as "live AND animal".

The "null" in the query above is a placeholder for a structured clause. There is no default - if you don't provide any structured clause you MUST put "null" in here. The structured clause allows you to restrict, or sort, your results. If I wanted to extend the query above to find only items costing less than $100, I could use

 WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', 'price < 100') > 0

and if I wanted the results with the newest items first, I could use

WHERE CATSEARCH (item_desc, '(toy dog) | "live animal"', 
                   'price < 100 order by start_time desc') > 0

Upvotes: 1

Related Questions