MSSQL Substring and keep the last word intact

Question

I have the following example string:

This string is very large, it has more then 160 characters. We can cut it with substring so it just has 160 characters but then it cuts of the last word that looks kind of stupid.

Now I want to have round about 160 characters, so I use substring()

SELECT SUBSTRING('This string is very large, it has more then 160 characters. 
We can cut it with substring so it just has 160 characters but then it cuts 
of the last word that looks kind of stupid.', 0 , 160) 

Wich results in:

This string is very large, it has more then 160 characters. We can cut it with substring so it just has 160 characters but then it cuts of the last word that l

Now I need to find a way to finish off the last word, in this case the word looks

Any Idea whats the best way to approach this problem?

Answer 1

DECLARE @S VARCHAR(500)= 'This string is very large, it has more then 160 characters. 
    We can cut it with substring so it just has 160 characters but then it cuts 
    of the last word that looks kind of stupid.'

SELECT CASE
         WHEN charindex(' ', @S, 160) > 0 THEN SUBSTRING(@S, 0, charindex(' ', @S, 160))
         ELSE @S
       END 

Answer 2

If you go with 160, your last word is that. If you go 165, your last word is looks. Here's how you can do it with 160:

declare @string varchar(1000)
select @string = 'This string is very large, it has more then 160 characters. 
We can cut it with substring so it just has 160 characters but then it cuts 
of the last word that looks kind of stupid.'

SELECT SUBSTRING(@string, 1, charindex(' ',@string,160)-1)

Note: This will error on strings less than 160 characters. See Martin Smith's answer for handling that situation.