The full text search feature allows you to search for terms that are near each other in a single column. This is called a proximity search. To perform a proximity search, you specify two terms with either the keyword NEAR between them, or the tilde (~).
You can specify an integer argument with the NEAR keyword to specify the maximum distance. For example, term1
NEAR term2 finds instances of term1 that are within five terms of term2. The order of terms is not significant;
'term1 NEAR term2' is equivalent to
'term2 NEAR term1'.
If you do not specify a distance, the database server uses 10 as the default distance.
You can also specify a tilde (~) instead of the NEAR keyword. For example,
'term1 ~ term2'. However, you cannot specify a distance when using the tilde form; the default of ten terms is applied.
You cannot specify a phrase as an argument in proximity searches.
In a proximity search, if you specify a prefix term as an argument, the proximity search is converted to an AND expression.
For example, on a 3-gram text index, searching for
'red NEAR appl*' is equivalent to searching for
'red AND "app ppl"'. Since this is no longer a proximity search, the search is no longer restricted to a single column in the case where multiple
columns are specified in the CONTAINS clause.
Suppose you want to search MarketingInformation.Description for the term fabric within 10 terms of the term skin. You can execute the following statement.
SELECT ID, "contains".score, Description FROM MarketingInformation CONTAINS ( Description, 'fabric ~ skin' );
Since the default distance is 10 terms, you did not need to specify a distance. By extending the distance by one term, however, another row is returned:
SELECT ID, "contains".score, Description FROM MarketingInformation CONTAINS ( Description, 'fabric NEAR skin' );
The score for ID 903 is higher because the terms are closer together.
|Discuss this page in DocCommentXchange.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|