CONTAINS
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.
(1) >>-CONTAINS--(--column-name--,--search-argument-----------------> >--+------------------------+--)------------------------------->< | (2) | '-,--string-constant-----'
- The SQL statement that invokes the CONTAINS function can be dynamically prepared by using a typed parameter marker for the search-argument, as in the following example: CONTAINS(C1,CAST(? AS CHAR(10))).
- string-constant must conform to the rules for the search-argument-options.
search-argument-options .---------------------------------. V (1) | |--------+-------------------------+-+--------------------------| +-QUERYLANGUAGE--=--value-+ +-RESULTLIMIT--=--value---+ | .-OFF-. | '-SYNONYM--=--+-ON--+-----'
- The same clause must not be specified more than once.
The schema is SYSIBM.
- column-name
- Specifies 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 that is 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. The underlying expression of the column of a view must be a simple column reference to the column of an underlying table, directly or through another nested view.
- search-argument
- Specifies an expression that returns a value that is a string value (except a LOB) that contains the terms to be searched for and must not be all blanks or the empty string. The actual length of the string must not exceed 4096 Unicode characters. 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.
- string-constant
- Identifies a string constant that specifies the search argument
options that are in effect for the function.
The options that can be specified as part of the search-argument-options are as follows:
- QUERYLANGUAGE = value
- Specifies the query language. The value can be any of the supported language codes. If the QUERYLANGUAGE option is not specified, the default is the language value of the text search index that is used when this function is invoked. If the language value of the text search index is AUTO, the default value for QUERYLANGUAGE is en_US.
- RESULTLIMIT = value
- Specifies the maximum number of results to be returned from the
underlying search engine. The value can be an integer
value between 1 and 2 147 483 647. If the RESULTLIMIT option is not
specified, no result limit is in effect for the query.
This scalar function cannot be called for each
row of the result table, depending on the plan that the optimizer
chooses. This function can be called once for the query to the underlying
search engine, and a result set of all of the 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 ?? ROWS from
the underlying text search engine and can be used as an optimization.
If the search engine is called for each row of the result because
the optimizer determines that is the best plan, then the RESULTLIMIT
option has no effect. Also, the RESULTLIMIT option has no effect when
the CONTAINS function is used along with the comparison operators
(<, >, <=, and >=) or the equality operator (=) and a value
of 0 (zero).
- SYNONYM = OFF or SYNONYM = ON
- Specifies whether to use a synonym dictionary that is associated
with the text search index. Use the Synonym Tool to add a synonym
dictionary to the collection. The default is OFF.
- OFF
- Do not use a synonym dictionary.
- ON
- Use the synonym dictionary that is 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 the null value. If the third argument is null, the result is as if the third argument was not specified.
The result is 1 if the document contains a match for the search criteria that are specified in the search argument. Otherwise, the result is 0.
CONTAINS is a non-deterministic function.
Examples
Example 1: Assume that information about employees' skills are stored in a table named EMP_RESUME. The following statement finds all of the employees who have "COBOL" in their resume. The text search argument is not case-sensitive.
SELECT EMPNO
FROM EMP_RESUME
WHERE RESUME_FORMAT = 'ascii'
AND CONTAINS(RESUME, 'cobol') = 1 Example 2: The search argument does not need to be a string constant. The search argument can be any SQL string expression, including a string contained in a host variable.
The following statement searches for the exact term "ate" in the COMMENT column:
char search_arg[100]; /* input host variable */
...
EXEC SQL DECLARE C3 CURSOR FOR
SELECT CUSTKEY
FROM K55ADMIN.CUSTOMERS
WHERE CONTAINS(COMMENT, :search_arg)= 1
ORDER BY CUSTKEY;
strcpy(search_arg, "ate");
EXEC SQL OPEN C3;
... Example 3: The following statement finds 10 students at random who wrote online essays that contain the phrase "fossil fuel" in Spanish, which is "combustible fósil." These students will be invited for a radio interview.
Use the synonym dictionary that was created for the associated text search index. Because only 10 students are needed, you can optimize the query 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