I had a project the other day that required me to shorten a description down to a certain length and add an elipsis. The problem I encountered was making sure that the truncation did not happen in the middle of a word.
So using a Postgres database, I used the following query to return the shortened description without ending in a partial word.
select
case when length(long_description)>= 100
then reverse(substring(reverse(left(long_description,100)),position(' ' in reverse(left(long_description,100))))) || '...'
else long_description
end
from data_feed
The steps involved were to
- Check if the field is over 100 characters, if >100, then continue to step 2
- Use left function to get 100 characters of the string
- Reverse the string returned from left
- Get a substring of the reversed truncated string from the first space (‘ ‘) to the end of the reversed truncated string
- Reverse the final string (So it is back to the correct direction of text)
- Append the ellipsis to the string ( or add a link or leave off if no extra chars are required)