SQL 2008 Full Text Search Word Proximity

Hello Folks,

I have been reading the Full Text help files for CONTAINS, FREETEXT, CONTAINSTABLE and so forth on MSDN and elsewhere, but I am not able to find the solution I am looking for.

I would like to be able to query with the following criteria:

Example value: "The quick brown fox jumped over the lazy dogs."

Select * from MyText where CONTAINS(column, 'brown near lazy').

I want to be able to return results where the word brown is within an n number of words from lazy, so for example, brown is within 5 words of lazy.

Is this even possible with the full text search, and if so, can someone provide an example?

more ▼

asked Aug 05, 2010 at 02:39 PM in Default

Jason Heine gravatar image

Jason Heine
3 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest


The explanation for NEAR in BOL states:

NEAR indicates the logical distance between terms, rather than the absolute distance between them.*For example, terms within different phrases or sentences within a paragraph are treated as farther apart than terms in the same phrase or sentence, regardless of their actual proximity, on the assumption that they are less related. Likewise, terms in different paragraphs are treated as being even farther apart.*

I understand this to mean that you cannot say how near a term may be. "Nearness" is a calculated value which you cannot override.

The only method that comes close is to use the ranking option. You use the CONTAINSTABLE command and specify a rank filter (see examples B & C in BOL). This would limit the results to the n closest values:

/*Create a table result, using the table MyTextTable and column MyTextColumn. Find top 5 ranked results for "brown" that is near to "lazy"*/
Select * from MyTextTable 
INNER JOIN CONTAINSTABLE(MyTextTable , MyTextColumn, 'brown NEAR lazy',5) AS FT_RESULT
on MyText.Id = FT_RESULT.[Key]
more ▼

answered Aug 06, 2010 at 01:47 AM

WilliamD gravatar image

25.9k 17 19 41

Thanks for the information. I will check out the ranking information.
Aug 06, 2010 at 06:51 AM Jason Heine
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 05, 2010 at 02:39 PM

Seen: 1777 times

Last Updated: Aug 05, 2010 at 02:39 PM