title: SQL query to extract words from a text field
body: <p>I am creating a SQL script wherein I have to output n (user input) number of words from the text field (varchar(max)) starting from the word (search terms) specified in a different table. So for example, my text string is "The quick brown fox jumps over the lazy dog" and I want to output 3 words from the word 'brown'. So, my output should be "brown fox jumps over".</p>
  
  <p>This operation needs to run on a set of two different tables. First table will contain the text field from multiple documents. Second table will consists of a number of search terms(the word 'brown' in above scenario) starting from which we will generate the output. (Please let me know if I am unclear with this part).</p>
  
  <p>I have made a code but have gone wrong somewhere with the syntax or understanding the SQL bit. Below is a snippet of the code that is generating the error.</p>
  
  <pre><code>        SELECT @Loopcounter = 0,
          @Termcount = Count([termName])
          FROM #temp_table -- temp_table contains the list of search terms
  
          WHILE ( @Loopcounter &lt; @Termcount )
          BEGIN 
  
          SELECT @SearchTerm = [termName] FROM #temp_table ORDER BY RowNum 
          OFFSET @Loopcounter ROWS
          FETCH NEXT 1 ROWS ONLY  -- to iterate all the search terms in temp_table on all documents
  
          SET @Spacecounter = 0;
          SET @Lastword = 0;
  
          SELECT 
              [DocID],
              [ExtractedText],
              (
              SELECT @Lastword = CHARINDEX( @SearchTerm, ExtractedText ) --position of search term in text field of a document
              FROM [EDDSDBO].[Document] t1 --contains the list of all documents and their text fields
              WHERE d.ArtifactID = t1.ArtifactID  -- to match the document id outside of the loop
  
              WHILE ( @Spacecounter &lt;= @Proxnum ) --@Proxnum is the number of words required by the user
              -- this loop will find spaces after the search term and will give the position of @proxnum space after the search term
              BEGIN
  
              SELECT @Lastword = CHARINDEX( ' ', ExtractedText, @Lastword ) --to find the first space after the search term
              FROM [EDDSDBO].[Document] t2
              WHERE d.ArtifactID = t2.ArtifactID
  
              SET @Lastword = @Lastword + 1
              SET @Spacecounter = @Spacecounter + 1
  
              END
  
              SELECT SUBSTRING ( ExtractedText, CHARINDEX( @SearchTerm, ExtractedText ), @Lastword - CHARINDEX( @SearchTerm, ExtractedText ) )
              FROM [EDDSDBO].[Document] t3 --to extract the words from starting of search term till the numbers of words required
              WHERE d.ArtifactID = t3.ArtifactID
              )
              AS [After Hit]
          FROM [EDDSDBO].[Document] d
          WHERE CONTAINS ( ExtractedText, @SearchTerm) --to only search the document that contains that search term
  
          SET @Loopcounter = @Loopcounter + 1
  
          END
  </code></pre>
  
  <p>I know that there is a lot of script there with not much context, but if anyone can help me out with this, please post in your answers. I assume that I went wrong in calling the loop inside the select statement, but I did not see an alternative for that.</p>
  
  <p>Let me know if you need more context in order to understand the requirements of this SQL script.
  THANKS!</p>