DB2 10.5 for Linux, UNIX, and Windows

Issuing a query

Issuing a query of the CONTAINS function searches for text in columns found in tables.

Example

This example demonstrates how the CONTAINS function searches for text in column comment in table texttab. The function returns 1 if the text satisfies the search argument, otherwise it returns 0.

SELECT AUTHOR,TITLE
        FROM DB2EXT.TEXTTAB
        WHERE CONTAINS(COMMENT, '"book"') = 1

In this example, you search for the term book in the column COMMENT.

Searching for "" is not supported. Using two consecutive quotation marks in a search term will result in a syntax error message. Also, a query syntax error occurs if a newline character is used within the search string.

Note:

If you know that the text search alone will return a very large result set, it is beneficial to add restrictive search criteria, for example:

SELECT AUTHOR,TITLE
        FROM db2ext.texttab
        WHERE CONTAINS(COMMENT, '"book"') = 1 AND PRICE < 20