When I use the query: LEFT(LEFT(@Text, 1000), 1001 - CHARINDEX('.', REVERSE(LEFT(@Text, 1000)))) Narrative, against the paragraph that is in the declare statement, it allows me to extract the first 1000 characters or to the nearest period.

Using this, the last line of extracted text is ‘The its enable direct men depend highly.’

What I am trying to figure out is how to create another query that will take up where the 1000 characters or period stops, and will begin with the next line which is ‘Ham windows sixteen who inquiry fortune demands.’ and will continue until the end of the text.

In my efforts so far I have managed to get it to start from the next paragraph down which begins with ‘Is be upon … ‘.

I should be home for the evening, and will continue to work on this. However any advice would be appreciated.

I am on SQL 2019.

Thank you to those who commented and those who have challenged me. In the hope of sharing, I have a better explanation of the problem and I have included the solution within this response.

I am working on a report where the narrative needs to fit within a set space on a report. The report is being built using Microsoft Report Builder.

If the narrative exceeds the space it needs to overflow into a different document.

The first part of the puzzle was to extract part of the narrative that will fit within the face sheet. The solution for this comes from:

https://dba.stackexchange.com/questions/121206/string-function-to-get-left-1000-but-only-to-last-period-or-carriage-return/324359?noredirect=1#comment632652_324359

The second part of the puzzle is to then extract the remaining narrative so it can be added to a different document.

I am running SQL 2019.

I have included the query with a DECLARE statement that contains the sample narrative below. As a result there are no INSERT scripts included for duplication. Just copy/paste the code below to test.

Included is the solution to the second part of the problem and the extraction of the narrative which will be in a different document.

Declare @Text Varchar(max) = '*Up unpacked friendly ecstatic so possible humoured do. Ample ended might folly quiet one set spoke her. We no am former valley assure. Four need spot ye said we find mile. Are commanded him convinced dashwoods did estimable forfeited. Shy celebrated met sentiments she reasonably but. Proposal its disposed eat advanced marriage sociable. Drawings led greatest add subjects endeavor gay remember. Principles one yet assistance you met impossible. The who arrival end how fertile enabled. Brother she add yet see minuter natural smiling article painted. Themselves at dispatched interested insensible am be prosperous reasonably it. She boisterous use friendship she dissimilar considered expression. Sex quick arose mrs lived. Mr things do plenty others an vanity myself waited to. Always parish tastes at as mr father dining at. Led ask possible mistress relation elegance eat likewise debating.

Or am nothing amongst chiefly address. The its enable direct men depend highly. Ham windows sixteen who inquiry fortune demands.

Is be upon sang fond must shew. Really boy law county she unable her sister. Feet you off its like like six. Among sex are leave law built now. In built table in an rapid blush. Merits behind on afraid or warmly. In either so spring wished. Melancholy wayBy message*';

With CTE As 
(
Select
   /* allows me to see LEN of the Narrative I am using */
   LEN(@Text) CharacterCount,
   /* allows me to see approx LEN of the Narrative that will flow into the second document */
   LEN(@Text) - 1000 Var,
   /* allows me to proof read the results in report builder to compare the full uncut narrative with the Start and Ending Narrative */
   @Text FullNarrative,
   /* Solution to Part 1: This code below was a written by Stack Exchange Contributor Solomon Rutzky. The PATINDEX values were expanded upon. 
      This provides the start of the Narrative to a breaking point of either 1000 characters or a specified nearest character. */
   LEFT(LEFT(@Text, 1000), 1001 - PATINDEX('%[ .,!?;-]%', REVERSE(LEFT(@Text, 1000)))) StartNarrative    
)
Select
   *,
   /* Solution to Part 2: This extracts the ending part of the Narrative which overflows. Thank you CS! */ 
   TRIM(RIGHT(@Text,CharacterCount - LEN(StartNarrative))) AS EndNarrative
From CTE