How 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. However, if you run these examples in Interactive SQL they will fail because you have not built a text index on MarketingInformation.Description yet.

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

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

Types of full text searches

Using full text search, you can search for terms, phrases (sequences of terms), or prefixes. You can also combine multiple terms, phrases, or prefixes into boolean expressions, or require that expressions appear near to each other with proximity searches.

You perform a full text search using a CONTAINS clause in either a WHERE clause or a FROM clause of a SELECT statement. You can also perform a full text search as part of the IF search condition (for example, SELECT IF CONTAINS...).

The following sections show you how to perform the different types of full text search available in SQL Anywhere.

Term and phrase searching
Prefix searching
Proximity searching
Boolean searching
Fuzzy searching
View searching
Tutorial: Performing a full text search on a GENERIC text index
Tutorial: Performing a fuzzy full text search
Tutorial: Performing a full text search on an NGRAM text index

Discuss this page in DocCommentXchange.