When you include a CONTAINS clause in the FROM clause of a query, each match has a score associated with it. The score indicates how close the match is, and you can use score information to sort the data.
Scoring is based on two main criteria:
Number of times a term appears in the indexed row The more times a term appears in an indexed row, the higher its score.
Number of times a term appears in the text index The more times a term appears in a text index, the lower its score. In Sybase Central, you can view how many times a term appears in the text index by viewing the Vocabulary tab for the text index. Choose the term column to sort the terms alphabetically. The freq column tells you how many times the term appears in the text index.
Then, depending on the type of full text search, other criteria impact scoring. For example, in proximity searches, the proximity of search terms impacts scoring.
By default, the result set of a CONTAINS clause has the correlation name contains that has a single column in it called score. You can refer to
"contains".score in the SELECT list, ORDER BY clause, or other parts of the query. However, because contains is a SQL reserved word, you must
remember to put it in double quotes. Alternatively, you can specify another correlation name such (for example,
CONTAINS ( expression ) AS ct). In the documentation examples for full text search, the score column is referred to as
The following statement searches MarketingInformation.Description for terms starting with stretch or terms starting with comfort:
SELECT ID, ct.score, Description FROM MarketingInformation CONTAINS ( MarketingInformation.Description, 'stretch* | comfort*' ) AS ct ORDER BY ct.score DESC;
Item 910 has the highest score because it contains two instances of the prefix term comfort, whereas the others only have one instance. As well, item 910 has an instance of the prefix term stretch.
The following example shows you how to perform a full text search across multiple columns and score the results:
Create an immediate text index on the Products table as follows:
CREATE TEXT INDEX scoringExampleMult ON Products ( Description, Name );
Perform a full text search on the Description and Name columns for the terms cap or visor, as follows. The result of the CONTAINS clause is assigned the correlation name ct, and is referenced in the SELECT list so that it is included in the results. Also, the ct.score column is referenced in the ORDER BY clause to sort the results in descending order by score.
SELECT Products.Description, Products.Name, ct.score FROM Products CONTAINS ( Products.Description, Products.Name, 'cap OR visor' ) ct ORDER BY ct.score DESC;
|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.
For other examples in the documentation to work properly, you must delete the text index you created on the Products table. To do so, execute the following statement:
DROP TEXT INDEX scoringExampleMult ON Products;
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|