Proximity searching

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

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' );

ID	score	Description
902	1.5572371866083279	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This simple, sleek, and lightweight technical shirt is designed for high-intensity workouts in hot and humid weather. The recycled polyester fabric is gentle on the earth and soft against your skin.</span></p></body></html>`

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[11] skin' );

ID	score	Description
903	1.5787803210404958	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A sporty, casual shirt made of recycled water bottles. It will serve you equally well on trails or around town. The fabric has a wicking finish to pull perspiration away from your skin.</span></p></body></html>`
902	2.163125855043747	`<html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This simple, sleek, and lightweight technical shirt is designed for high-intensity workouts in hot and humid weather. The recycled polyester fabric is gentle on the earth and soft against your skin.</span></p></body></html>`

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.