What is full text search?

Full text search is a more advanced way to search a database. Full text search quickly finds all instances of a term (word) in a table without having to scan rows and without having to know which column a term is stored in. Full text search works by using text indexes. A text index stores positional information for all terms found in the columns you create the text index on. Using a text index can be faster than using a regular index to find rows containing a given value.

Full text search capability in SQL Anywhere differs from searching using predicates such as LIKE, REGEXP, and SIMILAR TO, because the matching is term-based, not pattern-based.

String comparisons in full text search use all the normal collation settings for the database. For example, if the database is configured to be case insensitive, then full text searches will be case insensitive.

Except where noted, full text search leverages all the international features supported by SQL Anywhere.

To perform a full text search on a database containing Chinese, Japanese, and Korean (CJK) data, see the white paper "Performing Full Text Searches on Chinese, Japanese, and Korean Data in SQL Anywhere 11" at [external link] http://www.sybase.com/detail?id=1061814.

Two ways to perform a full text search

You can perform a full text query either by using a CONTAINS clause in the FROM clause of a SELECT statement, or by using a CONTAINS search condition (predicate) in a WHERE clause. Both return the same rows; however, use a CONTAINS clause in a FROM clause also returns scores for the matching rows.

The following examples show how the CONTAINS clause and search condition are used in a query. These examples use the example MarketingInformation.Description text index that is provided in the sample database:

SELECT *
   FROM MarketingInformation CONTAINS ( Description, 'cotton' );

SELECT *
   FROM MarketingInformation
   WHERE CONTAINS ( Description, 'cotton' );

Considerations before using full text search

Here are some considerations to make when deciding whether to use full text indexes over regular indexes:

You cannot use aliases in a CONTAINS clause or a CONTAINS search condition.

When using duplicate correlation names in a query, a CONTAINS (FROM CONTAINS()) is only supported on the first instance of the correlation name. For example, the following syntax returns an error because of the second CONTAINS predicate involving A:

SELECT *
FROM CONTAINS(A contains-query-string) JOIN B ON A.x = B.x,
     CONTAINS(A contains-query-string) JOIN C ON A.y = C.y;

When using external term breaker and prefilter libraries, there are several additional considerations:

Querying and updating The external library must remain available for any operations that require updating, querying, or altering the text indexes built using the libraries.
Unloading and reloading The external library must be available during unloading and reloading of data of data associated with the full text index.
Database recovery The external library must be available to recover the database. This is because the database can not recover if there are operations in the transaction log that involved the external library since the last checkpoint.