Home » Server Options » Text & interMedia » Search for Boolean Operator (Oracle 10g)
Search for Boolean Operator [message #475956] Mon, 20 September 2010 00:51 Go to next message
asksrikanth@gmail.com
Messages: 9
Registered: December 2008
Location: Bangalore
Junior Member
Hi All,

I have a small problem in search for boolean operator if i am using that as a string search.

For example i have to search for string "Nutrients and food" exactly phrase. If i am writting query for that like

Select *
From TableName
Where Contains(Columnname,'"Nutrients and food"')>0

it is returning the rows that have individual words like 'Nutrients food', 'Nutrients and food' as well as 'Nutrients or food'.

Where my search result should only get the rows that contains "Nutrients and food" exactly.


Thanks in advance
Sri
Re: Search for Boolean Operator [message #476018 is a reply to message #475956] Mon, 20 September 2010 13:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
"And" is a default stopword. If you do not specify a stoplist, then the default stoplist is used and "and" is not tokenized. If you want to be able to search for "and", then you need to use an empty_stoplist. Also, "and" has special meaning to Oracle text. If you search for "word1 and word2", then it returns all rows with "word1" in the row and "word2" in the row. If you want "and" treated as part of the text, without special meaning, then you need to escape it by either enclosing the word "and" in curly brackets, like {and} or enclosing the whole phrase in curley brackets. The double quotes do not mean anything to Oracle Text. Please see the brief demo below.

SCOTT@orcl_11gR2> CREATE TABLE TableName
  2    (ColumnName  CLOB)
  3  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO TableName VALUES ('Nutrients and food')
  3  INTO TableName VALUES ('Nutrients or food')
  4  INTO TableName VALUES ('Nutrients food')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11gR2> CREATE INDEX test_idx
  2  ON TableName (ColumnName)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST')
  5  /

Index created.

SCOTT@orcl_11gR2> Select * From TableName
  2  Where  Contains
  3  	      (Columnname,
  4  	       '{Nutrients and food}') > 0
  5  /

COLUMNNAME
--------------------------------------------------------------------------------
Nutrients and food

1 row selected.

SCOTT@orcl_11gR2> Select * From TableName
  2  Where  Contains
  3  	      (Columnname,
  4  	       'Nutrients {and} food') > 0
  5  /

COLUMNNAME
--------------------------------------------------------------------------------
Nutrients and food

1 row selected.

SCOTT@orcl_11gR2> 

Re: Search for Boolean Operator [message #476246 is a reply to message #476018] Wed, 22 September 2010 05:28 Go to previous messageGo to next message
asksrikanth@gmail.com
Messages: 9
Registered: December 2008
Location: Bangalore
Junior Member
Thank you very much, it is working but i have one doubt.
The parameter clause PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST') will only remove the stoplist for this index alone is my assumption right?
because we use many other databases which it should not be problem if we remove stoplist.
Thanks again

[Updated on: Wed, 22 September 2010 05:29]

Report message to a moderator

Re: Search for Boolean Operator [message #476247 is a reply to message #476246] Wed, 22 September 2010 05:48 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, yes - it is valid only for that CREATE INDEX statement. Other indexes remained as they were.
Re: Search for Boolean Operator [message #476263 is a reply to message #476246] Wed, 22 September 2010 06:52 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
asksrikanth@gmail.com wrote on Wed, 22 September 2010 03:28

The parameter clause PARAMETERS ('STOPLIST CTXSYS.EMPTY_STOPLIST') will only remove the stoplist for this index alone is my assumption right?


Right.
Previous Topic: Will "Contain clause" degrade the performance?
Next Topic: Problem with special chars in BLOB data type using contains keyword
Goto Forum:
  


Current Time: Thu Mar 28 04:54:28 CDT 2024