The CONTAINS function searches a text search index using criteria that are specified in a search argument and returns a result about whether or not a match was found.
>>-CONTAINS--(--column-name--,--search-argument--+----------------------------+--)->< '-,--search-argument-options-'
search-argument-options .-------------------------------. V | (1) |----+-QUERYLANGUAGE-- = --value-+-+----------------------------| +-RESULTLIMIT-- = --value---+ | .-OFF-. | '-SYNONYM-- = --+-ON--+-----'
bytes after conversion
to Unicode
and must not exceed the text search limitations or
number of
terms as specified in the search argument
syntax. For information on search-argument syntax,
see Text search argument syntax.CONTAINS may or may not be called for each row of the result table, depending on the plan that the optimizer chooses. If CONTAINS is called once for the query to the underlying search engine, a result set of all of the ROWIDs or primary keys that match are returned from the search engine. This result set is then joined to the table containing the column to identify the result rows. In this case, the RESULTLIMIT value acts like a FETCH FIRST n ROWS ONLY from the underlying text search engine and can be used as an optimization. If CONTAINS is called for each row of the result because the optimizer determines that is the best plan, then the RESULTLIMIT option has no effect.
If search-argument-options is the empty string or the null value, the function is evaluated as if search-argument-options were not specified.
The result of the function is a large integer. If search-argument can be null, the result can be null; if search-argument is null, the result is the null value.
The result is 1 if the column contains a match for the search criteria specified by the search-argument. Otherwise, the result is 0. If the column contains the null value or search-argument contains only blanks or is the empty string, the result is 0.
CONTAINS is a non-deterministic function.
Rules: If a view, nested table expression, or common table expression provides a text search column for a CONTAINS or SCORE scalar function and the applicable view, nested table expression, or common table expression has a DISTINCT clause on the outermost SELECT, the SELECT list must contain all the corresponding key fields of the text search index.
If a view, nested table expression, or common table expression provides a text search column for a CONTAINS or SCORE scalar function, the applicable view, nested table expression, or common table expression cannot have a UNION, EXCEPT, or INTERSECT at the outermost SELECT.
If a common table expression provides a text search column for a CONTAINS or SCORE scalar function, the common table expression cannot be subsequently referenced again in the entire query unless that reference does not provide a text search column for a CONTAINS or SCORE scalar function.
CONTAINS and SCORE scalar functions are not allowed if the query specifies:
a table with a read trigger, or
SELECT EMPNO
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
AND CONTAINS(RESUME, 'cobol') = 1
SELECT FIRSTNME, LASTNAME
FROM STUDENT_ESSAYS
WHERE CONTAINS(TERM_PAPER, 'combustible fósil',
'QUERYLANGUAGE = es_ES RESULTLIMIT = 10 SYNONYM = ON') = 1
char search_arg[100];
...
EXEC SQL DECLARE C1 CURSOR FOR
SELECT CUSTKEY
FROM CUSTOMERS
WHERE CONTAINS(COMMENT, :search_arg) = 1
ORDER BY CUSTKEY;
strcpy(search_arg, "ate");
EXEC SQL OPEN C1;