The CONTAINS function searches a text search index using
criteria that you specify in a search argument and returns a value
that indicates whether a match is found.
Function syntax
>>-CONTAINS--(--column-name--,--search-argument----------------->
>--+------------------------+--)-------------------------------><
| (1) |
'-,--string-constant-----'
Notes:
- string-constant must conform to the rules
for search-argument-options.
search-argument-options
.----------------------------------.
V (1) |
|--------+- ------------------------+-+-------------------------|
+-QUERYLANGUAGE--=--locale-+
+-RESULTLIMIT--=--value----+
| .-OFF-. |
'-SYNONYM--=--+-ON--+------'
Notes:
- You cannot specify the same clause more than once.
The schema is SYSIBM.
Function parameters
- column-name
- A qualified or unqualified name of a column that has a text search
index that is to be searched. The column must exist in the table or
view identified in the FROM clause in the statement and the column
of the table, or the column of the underlying base table of the view,
must have an associated text search index (SQLSTATE 38H12). The underlying
expression of the column of a view must be a simple column reference
to the column of an underlying table, either directly or through another,
nested view.
- search-argument
- An expression that returns a value that is a string value (except
a LOB) that contains the terms to be searched for and is not all blanks
or the empty string (SQLSTATE 42815). The string value that results
from the expression should be less than or equal to 4096 bytes (SQLSTATE
42815). The value is converted to Unicode before it is used to search
the text search index. The maximum number of terms per query must
not exceed 1024 (SQLSTATE 38H10).
- string-constant
A string constant that specifies the search argument
options that are in effect for the function.
The options
that you can specify as part of the search-argument-options are
as follows:
- QUERYLANGUAGE = locale
- Specifies the locale that the DB2® Text Search engine
uses when performing a text search on a DB2 text
column. The value can be any of the supported locales. If you do not
specify QUERYLANGUAGE, the default is the locale
of the text search index. If the LANGUAGE parameter
of the text search index is AUTO, the default value
for QUERYLANGUAGE is en_US.
- RESULTLIMIT = value
If the optimizer chooses a plan that calls the search
engine for each row of the result set to obtain the SCORE, then the RESULTLIMIT option
has no effect on performance. However, if the search engine is called
once for the entire result set, RESULTLIMIT acts
like a FETCH FIRST clause.
When using multiple text
searches that specify RESULTLIMIT in the same
query, use the same search-argument. If you use
different search-argument values, you might not
receive the results that you expect.
For partitioned text indexes,
the result limit is applied to each partition separately.
- SYNONYM = OFF | ON
- Specifies whether to use a synonym dictionary that is associated
with the text search index. The default is OFF. To
use synonyms, add the synonym dictionary to the text search index
using the Synonym Tool.
- OFF
- Do not use a synonym dictionary.
- ON
- Use the synonym dictionary associated with the text search index.
The result of the function is a large integer. If the second argument can be
null, the result can be null; if the second argument is null, the
result is null. If the third argument is null, the result is as if
you did not specify the third argument. CONTAINS returns the integer
value 1 if the document contains a match for the criteria specified
in the search argument. Otherwise, it returns 0.
CONTAINS is
a non-deterministic function.
Note: You must take additional steps when using parameter markers
as a search argument inside the text search functions. Parameter markers
do not have a type when precompiled in JDBC and ODBC programs, but
the search argument in the text search functions must resolve to a
string value. Because the unknown type of the parameter marker cannot
be resolved to a string value (SQLCODE -418), you must explicitly
cast the parameter marker to the VARCHAR data type.
Examples
- The following query is used to find all of the employees who have
COBOL in their resumes. The text search argument is not case-sensitive.
SELECT EMPNO
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
AND CONTAINS(RESUME, 'COBOL') = 1
- In the following C program, the exact term ate is searched for in the COMMENT column:
char search_arg[100]; /* input host variable */
...
EXEC SQL DECLARE C3 CURSOR FOR
SELECT CUSTKEY
FROM CUSTOMERS
WHERE CONTAINS(COMMENT, :search_arg) = 1
ORDER BY CUSTKEY;
strcpy(search_arg, "ate");
EXEC SQL OPEN C3;
...
- The following query is used to find any 10 students who wrote
online essays that contain the phrase fossil fuel in Spanish, which is combustible fósil. A synonym
dictionary was created for the associated text search index. Because
only 10 students are needed, the query is optimized by using the RESULTLIMIT option to limit the number of results from
the underlying text search server.
SELECT FIRSTNME, LASTNAME
FROM STUDENT_ESSAYS
WHERE CONTAINS(TERM_PAPER, 'combustible fósil',
'QUERYLANGUAGE= es_ES RESULTLIMIT = 10 SYNONYM=ON') = 1