How does contains() in PL-SQL work?
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), valueFROM table_nameWHERE 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 A1 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.
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;