Return surrounding text for phrase found in full-text search, SQL 2005

Question

I'm using a contains predicate to find phrases in a SQL Server indexed text field. Is there a way to return the portion of the text field that contains the searched phrase, or some area around it?

For example, if I'm searching for "all men are created equal" in the Gettysburg address (excerpted below), I'd like to return "dedicated to the proposition that all men are created equal" e.g. some text around it.

Four score and seven years ago our fathers brought forth on this continent, a new nation, conceived in Liberty, and dedicated to the proposition that *all men are created equal.*

Now we are engaged in a great civil war, testing whether that nation, or any nation so conceived and so dedicated, can long endure. We are met on a great battle-field of that war. We have come to dedicate a portion of that field, as a final resting place for those who here gave their lives that that nation might live. It is altogether fitting and proper that we should do this.

Answer 1

Well, I'm not familiar with SQL Server sintax, but you could find the occurrence inside the field and return a substring for it. Pseudo-code:

SELECT
  SUBSTRING(field, MAX(0, STRPOS(field, 'all men are equal' - 20), STRLEN('all men are equal') + 40)
FROM
  yourtable
WHERE
  field CONTAINS 'all men are equal'

With this you're finding the position of the substring only for those records containing the phrase and returning a string 40 chars longer, so something like that should work.