Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » Querying data » Full text search


Scoring full text search results

If you use a CONTAINS query in the FROM clause, then an additional score column is available for use in the SELECT list, ORDER BY clause, or other parts of the query. By default, the score column has the correlation name, contains. You can optionally specify a different correlation name following the closing bracket of the CONTAINS query. A higher score corresponds to a better match. Therefore, it is often useful to order by descending score to get the best matches first.

Example 1: Searching one column

For example, create a text index on the Products.Description column as follows:

CREATE TEXT INDEX scoringExample 
   ON Products ( Description );

Query the text index as follows. Note that the results of the CONTAINS clause can optionally be followed by a correlation name used to reference the score columns from elsewhere in the SELECT statement.

SELECT Products.Description, ct.* 
    FROM Products CONTAINS ( Products.Description, 'cap OR cotton' ) ct 
    ORDER BY ct.score DESC;
Description score
Cotton Cap 3.3664304042764206
Wool cap 1.8224517537856726
Cotton Shorts 1.5101504250714481

Note that Cotton Cap has a high score in the results because it contains both search terms.

Example 2: Searching multiple columns

When you perform a full text search across multiple columns, the result includes a score for the row. For example, create a text index on Products.Description and Products.Name columns as follows:

CREATE TEXT INDEX scoringExampleMult 
   ON Products ( Description, Name );

Query the Description and Name columns for the terms cap or visor.

SELECT Products.Description, Products.Name, ct.score 
    FROM Products CONTAINS ( Products.Description, 
                             'cap OR visor' ) ct 
    ORDER BY ct.score DESC;
Description Name score
Cloth Visor Visor 3.5635154905713042
Plastic Visor Visor 3.4507856451176244
Wool cap Baseball Cap 3.2340501745357333
Cotton Cap Baseball Cap 3.090467108972918

The scores for a multi-column search are calculated as if the column values were concatenated together and indexed as a single value. Note, however, that phrases and NEAR operators never match across column boundaries, and that a search term that appears in more than one column increases the score more than it would in a single concatenated value.