How does contains() in PL-SQL work?

Question

Have a lot of unnecessary results using contains() method in my query. Don't tell me to use like or something else. It is hardcoded and couldn't be changed.

Answer 1

Contains is used on text fields that have a 'CONTEXT Index', which indexes a text field for searching. The standard usage is like this (using the score operator to display what is returned from the contains clause based on the 1 in contains matching the 1 in score):

SELECT score(1), value
FROM table_name
WHERE CONTAINS(textField, 'searchString', 1) > 0;

For data like this in table table_name

value  |  textField
-------|-----------------------------------------------
A      |   'Here is searchString.  searchString again.'
B      |   'Another string'
C      |   'Just one searchString'

That query would return

2 A
1 C

So contains is similiar to like, but will count how many times a string occurs in a text field. I couldn't find a resource using Contains the way it is used in the query you posted, but I think that would return rows where dFullText has at least one instance of car in it, or the equivalent of this sql:

Select * from blabla where dFullText like "%car%"

Here is another source.

Answer 2

See this example from oracle.com

declare 
rowno number := 0; 
   begin 
   for c1 in (SELECT SCORE(1) score, title FROM news 
          WHERE CONTAINS(text, 'oracle', 1) > 0
          ORDER BY SCORE(1) DESC) 
   loop 
   rowno := rowno + 1; 
   dbms_output.put_line(c1.title||': '||c1.score); 
exit when rowno = 10; 
end loop; 
end;