Meaningful string limiting

I've got rows in my table that contain text anywhere between 31 character and 281 characters long.

Rather than display all this text in my table on my webpage, I'd like to limit this text to a few characters long so as to identify it to the user.

What I've done is select left(ColumnName, 30) but this way is fairly ugly on the page as it cuts off the text in the middle of words. Can anyone suggest a way to break it on a character or something that would displa开发者_如何转开发y better on my page?

---

Code:

declare @columnname nvarchar(281)
set @columnname = 'Rather than display all this text in my table on my webpage, I''d like to limit this text to a few characters long so as to identify it to the user.'

select case when charindex(' ', @columnname, 30) > 30 
  then left(@columnname, charindex(' ', @columnname, 30)) else @columnname end

Result:

Rather than display all this text 

---

One way to handle it would be to modify your existing solution with the CHARINDEX function, as below:

select left(ColumnName, charindex(' ', ColumnName, 30) - 1)
from TableName

This will find the first space starting at position 30, and return everything to the left of that.

For more, see here: http://msdn.microsoft.com/en-us/library/ms186323.aspx