MSSQL Substring and keep the last word intact

sql, sql-server, substring

Solution

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

Problem

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?

Original source