For the original requirement -- 1000 max characters, up to the last period, but there might not be any period -- the following would work:
LEFT(LEFT(string, 1000),
1001 - CHARINDEX('.', REVERSE(LEFT(string, 1000))))
The LEFT(string, 1000) is used as the expression to take the LEFT(expression, some number) from because it ensures that no more than 1000 characters will be taken which would otherwise happen in the case of the string not having any periods at all. While the LEFT(expression, 1000) could have instead been wrapped around the entire LEFT(string, 1001 - ...), doing it the way shown above should allow for the expression to be reused since it also shows up exactly the same inside of the REVERSE function, hence a little more efficient.
For the updated requirement of introducing a Carriage Return as a fall-back character to look for, you can maybe switch to using PATINDEX as it can look for a list of characters:
LEFT(LEFT(string, 1000),
1001 - PATINDEX('%[.' + CHAR(13) + ']%', REVERSE(LEFT(string, 1000))))
However, there is one final requirement added:
Should there be both periods and Char(13)'s I would ideally like it to cut if off at the period.
Unfortunately, PATINDEX can't distinguish which one comes first. At this point it seems like some form of conditional logic will need to be added :-(.
LEFT(LEFT(TestData, 1000), 1001 - IIF(CHARINDEX('.', REVERSE(LEFT(TestData, 1000))) > 0,
CHARINDEX('.', REVERSE(LEFT(TestData, 1000))),
CHARINDEX(CHAR(13), REVERSE(LEFT(TestData, 1000))))
)
Here I not only reused the LEFT(TestData, 1000), but the REVERSE(LEFT(TestData, 1000)) shows up 3 times, and for the IIF (which is really just shorthand for CASE WHEN x THEN y ELSE x END) I reused the entire CHARINDEX('.', REVERSE(LEFT(TestData, 1000))).
The example code is posted on Pastebin.com at:
Get 1000 chars max, up to final period, else to final return
The example shows the outcome of the 3 methods shown above across 7 different test cases.