Search for Boolean Operator [message #475956] |
Mon, 20 September 2010 00:51 |
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 |
|
Barbara Boehmer
Messages: 9102 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 |
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 #476263 is a reply to message #476246] |
Wed, 22 September 2010 06:52 |
|
Barbara Boehmer
Messages: 9102 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.
|
|
|