0

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')
Dale K
  • 25,246
  • 15
  • 42
  • 71
David Olsen
  • 19
  • 3
  • 2
    SQL really doesn't feel like the correct tool for this; especially not T-SQL with its very simple string manipulation functions. Use something that supports Regex. This is overly broad as well; for example what happens if `'crisis'` is found more than once? – Thom A Mar 23 '21 at 20:35

1 Answers1

-1

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.

Carson
  • 2,700
  • 11
  • 24
  • You seriously rock like Van Halen!! – David Olsen Mar 23 '21 at 21:06
  • SELECT FileNames, SUBSTRING(CAST(DocumentBin AS VARCHAR(MAX)), PATINDEX('%disease%', CAST(DocumentBin AS VARCHAR(MAX)))-200, 300) FROM Tbl_Support_Documents WHERE FREETEXT(DocumentBin, 'disease') ; – David Olsen Mar 24 '21 at 21:43