You can specify multiple terms separated by Boolean operators when performing full text searches. SQL Anywhere supports the following Boolean operators when performing a full text search: AND, OR, and AND NOT.

Using the AND operator in full text searches

The AND operator matches a row if it contains all of the specified search terms. You can also use an ampersand (&) for the AND operator; the two are equivalent. If multiple terms are specified without an operator between them, AND is implied.

The order in which the terms are listed is not important.

For example, each of the following statements finds the rows in the Products table that contain the terms cotton and cap in the Description column.

SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton AND cap' );
SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton & cap' );
SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton cap' );

Using the OR operator in full text searches

The OR operator matches a row if it contains at least one of the specified search terms. You can also use a vertical bar (|) for the OR operator; the two are equivalent.

The order in which the terms are listed is not important.

For example, either statement below returns the rows in the Products table that contain either, or both, cotton or cap in the Description column.

SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton OR cap' );
SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton | cap' );

Using the AND NOT operator in full text searches

The AND NOT operator finds results that match the left argument and do not match the right argument. You can also use a hyphen (-) for the AND NOT operator; the two are equivalent.

For example, either statement below returns the rows in the Products table that contain cotton, but that do not contain cap, in the Description column.

SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton AND NOT cap' );
SELECT * 
   FROM Products 
   WHERE CONTAINS ( Products.Description, 'cotton - cap' );

Grouping terms and phrases

Terms and expressions can be grouped with parentheses. For example, the following statement searches the Description column for items that have cotton and cap, or that have visor.

SELECT * FROM Products
   WHERE CONTAINS( Product.Description, ' ( cotton AND cap ) OR visor ' );

You can also search across multiple columns in a single query, as shown in the following two SELECT statements:

SELECT * 
   FROM Products 
   WHERE CONTAINS( Products.Description, 'cap' ) 
      OR CONTAINS( Products.Name, 'visor' );

SELECT * 
   FROM Products 
      CONTAINS ( Products.Description, Products.Name, 'visor | cap' );

In the latter syntax, scores are assigned to the results. See Scoring full text search results.

Also, note that the two queries have different semantics. In the first query, a row matches if the Description column contains cap or the Name column contains visor. In the second query, a row matches if either column contains either visor or cap.

Using the FUZZY operator in full text searches

You can use the FUZZY operator followed by a string in double quotes to find an approximate match for the string. This is only allowed for text indexes configured to use the NGRAM term breaker.

The FUZZY operator is normally only useful for a full text query in the FROM clause that returns a score. This is because many strings may be approximate matches, but usually only the matches with the highest scores are close enough to be meaningful matches.

Using the FUZZY operator is equivalent to breaking the string manually into n-grams and separating them with OR operators. For example, suppose you have a text index configured with the NGRAM term breaker and a MAXIMUM TERM LENGTH of 3. Specifying 'FUZZY "500 main street"' is equivalent specifying '500 OR mai OR ain OR str OR tre OR ree OR eet'.