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[5] 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[1] 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.

 Examples

SELECT ID, "contains".score, Description  
   FROM MarketingInformation CONTAINS ( Description, 'fabric ~ skin' );

SELECT ID, "contains".score, Description 
   FROM MarketingInformation CONTAINS ( Description, 'fabric NEAR[11] skin' );