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.

Answer 2

Just stumbled across this while trying to accomplish something similar. Building off of Seb's response, I implemented the following to solve our issue:

SELECT '...' + SUBSTRING(@TextToParse, CHARINDEX(@TheKeyword, @TextToParse)-150, 350) + '...'

This will return the keyword or phrase preceded by 150 characters. 350 characters will be returned in total. Modify those numbers as needed. Also the ellipses are included at the beginning and end since this code doesn't accommodate avoiding breakage in the middle of words.