For the original requirement -- 1000 max characters, up to the last period, but there might not be any period -- the following would work:

<!-- language: lang-sql -->

    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:

<!-- language: lang-sql -->

    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 :-(.

<!-- language: lang-sql -->

    LEFT(LEFT(TestData, 1000), 1001 - IIF(CHARINDEX('.', REVERSE(LEFT(TestData, 1000))) > 0,
                                          CHARINDEX('.', REVERSE(LEFT(TestData, 1000))),
                                          CHARINDEX(CHAR(13), 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][1]

The example shows the outcome of the 3 methods shown above across 7 different test cases.


  [1]: http://pastebin.com/2Ee5Lmp5