Occasionally you might have a scenario where you need to select only x leftmost number of characters from a column. We do this often when the screen real estate is limited or when showing data in a grid. But when you don't want to display the whole field, you can't just blindly select whatever number of characters you need using the LEFT() function. Well, you could, but not without potentially suffering from a side effect when certain words start taking on a different meaning after being truncated. Just think SELECT LEFT('... professional association ...', X) . Something like this actually happened on one of our sites, although it wasn't as bad as my example.
So the smart thing to do is to make sure the result never gets truncated in the middle of the word. To do that, you first need to get the position of the rightmost space character, then select everything up to that point and maybe append “...” to show that there is more text. Unfortunatelly T-SQL doesn't have something like LastIndexOf, you can only get the index of the first character using the CHARINDEX function. So the trick here is to use the REVERSE function first, get the index of the first space and subtract this number from the number of characters you need. Here is a sample query for selecting 100 leftmost characters and appending “...” if the Description field is longer than that:
SELECT CASE
WHEN LEN(Description) <= 100 THEN Description
ELSE LEFT(Description, 100 - CHARINDEX(' ',
REVERSE(LEFT(Description, 100)))) + '…'
END AS ShortDescription FROM MyTable