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 or BEFORE between them, or the tilde (~).
You can use proximity-expression to search for terms that are near each other. For example, b NEAR[2,5] c searches for instances of b and c that are at most 5 and at least 2 terms away from each other. The order of terms is not significant; b NEAR c' is equivalent toc NEAR b. If NEAR is specified without distance, a default of ten terms is applied. You can specify a tilde (~) instead of NEAR. This is equivalent to specifying NEAR without a distance so a default of 10 terms is applied. NEAR expressions cannot be chained together (for example, a NEAR[1] b NEAR[1] c).
BEFORE is like NEAR except that the order of terms is significant. b BEFORE c is not equivalent to c BEFORE b; in the former, the term b must precede c, while in the latter, the term b must follow c. BEFORE accepts both minimum and maximum distances (like NEAR). The default minimum distance is 1. The minimum distance, if given, must be less than or equal to the maximum distance; otherwise, an error is returned.
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 using an NGRAM text index, 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.
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.