Artic Artic - 6 months ago 12
SQL Question

How does contains() in PL-SQL work?

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

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.