Substring MSSQL and keep the last word unchanged

Question

I have the following line of example:

This line is very large, it has more than 160 characters. We can cut it with a substring, so that it has only 160 characters, but then it cuts out the last word, which looks like stupid.

Now I want to have 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) 

Result:

This line is very large, it has more than 160 characters. We can cut it with a substring so that it has only 160 characters, but then it cuts the last word, that l

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

Is any idea 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 from 160, your last word will be that . If you go over 165, your last word will be looks . Here 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 result in an error with lines less than 160 characters. See Martin Smith's answer for handling this situation.