Full text search can quickly find all instances of a term (word) in a database without having to scan table rows and without having to know which column a term is stored in. Full text search works by using text indexes, which you must create first. A text index stores complete positional information for every instance of every term in every indexed column. Using a text index to find rows that contain a term can be faster than scanning every row in the table for the same reasons that it can be faster to use a regular index to find rows containing a given value. See Text indexes.

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

Like regular indexes, text indexes consume disk space. Therefore, you should be careful not to create more text indexes than are required to support your queries.

Text indexes require a refresh strategy that reflects the acceptable amount of data staleness. Frequently refreshing many text indexes, especially large ones, can impact performance. See Creating text indexes.

Text indexes are created using settings stored in a text configuration object. Text configuration objects allow you to build text indexes that fit the type of text searching you anticipate. See Text configuration objects.

You can create text indexes on columns of any type. Columns that are not of type VARCHAR or NVARCHAR are converted to strings during indexing. See Data type conversions.

String comparisons in full text search use all of 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. See Understanding collations.

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

Performing a full text query

You can perform a full text query by using the CONTAINS clause in the FROM clause of a SELECT statement, or by using the CONTAINS search condition in a WHERE clause. Both return the same rows. However, the CONTAINS procedure also provides scores for the matching rows. Columns specified in a CONTAINS clause must be part of a text index. See Text indexes.

For example, the following two statements query the Description column in the Products table, and return the rows where the value in the Description column contains the term cotton. The second statement also returns scores for the matching rows.

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

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

See FROM clause, and CONTAINS search condition.

Reference links in the documentation