[SOLVED] Using SQL to Return Entire Paragraph if a word is found

Using SQL to Return Entire Paragraph if a word is found

I would like to return all the words surrounding the search term in a varbinary text enabled column. For example, if the word crisis is found, I would like to return the paragraph or at least 10 words on each side of the searched word. I am able to use the free text feature but I'm stumped as to how to return the text around it.

SELECT Filenames, DocumentBin
FROM Tbl_Support_Documents
WHERE FREETEXT(DocumentBin, 'crisis')

Solution

This SQL isn't designed for functionality in this way. It would most likely be faster to grab the entire document and then split out the paragraph on your end, or something else. The following query simply returns the surrounding n characters from a match.

As a note, FREETEXT searches for meaning matches, not exact text matches. The following answer assumes you are looking for exact text matches. There is no way to have this behavior with non-exact matching.

Regardless, answering your actual question:

SELECT Filenames,
    SUBSTRING(DocumentBin, PATINDEX('%crisis%', DocumentBin)-100, 200)
FROM blah blah

Using substring, we can limit Document bin to the 200 characters surrounding the match. From there, you should pick out however many words you care about on your end.