CONTAINS function

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

Notes:

¹ string-constant must conform to the rules for search-argument-options.

search-argument-options

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.

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
```