When you perform a proximity search with NEAR in versions of SQL Server prior to SQL Server 2012 "Denali," we decide what's NEAR for you. In SQL Server 2012, however, you're in control! With the new customizable NEAR operator, your full-text query can now specify 2 optional conditions that you couldn't specify before:
Here's a quick example of the new NEAR operator:
… CONTAINSTABLE (Documents, Content, 'NEAR((John, Smith), 4, TRUE)')
This is a new operator, not an enhancement to the existing NEAR operator. The generic NEAR operator is deprecated in SQL Server 2012. It continues to work, but should not be used in new development.
You can specify 2 or more search terms. As with generic NEAR, all search terms must be found in the target text to qualify as a hit.
What about noise words among the query terms?
CONTAINS(NEAR(('the', 'dog'), 3)) => CONTAINS('dog')
What about overlapping query terms? We only return non-overlapping occurrences. For example, if you performed the following query against the text 'AA' -
NEAR((A, AA), 2, TRUE)
- we will not return a hit, because the occurrences overlap. In fact, a query for overlapping terms can fail due to its complexity.
For detailed information about the optional max_gap argument, see my blog post, How near is NEAR in SQL Server 2012 Full-Text Search?
The optional Boolean order argument enforces order among the search terms. The default value is FALSE. If you want to specify order, then you also have to specify max_gap.
In Arabic, Hebrew, and other RTL languages, the NEAR semantics apply in reverse. For example, NEAR((John, Smith), 3, TRUE) will match documents that contains "Smith xx xx John."
You cannot use ISABOUT or FORMSOF with the custom proximity operator.
You cannot combine the new NEAR operator in a query with the generic ("old") NEAR operator.
However, you can use AND and OR, as shown in the following examples:
CONTAINS('NEAR((A, B), 5) AND C')
CONTAINS('NEAR((A, B), 5) OR C')
CONTAINS('NEAR((A, B), 5) AND/OR NEAR((C, D), 3)')
I hope this helps you to use the new NEAR operator in SQL Server 2012 successfully! Subscribe to our blog feed for ongoing updates about search capabilities in SQL Server.