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))))
)
For the example code, I am working on getting this into Pastebin...