SQL: Shorten string to length

By Eric Downing. Filed in SQL  |  
TOP del.icio.us digg

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

  1.  Check if the field is over 100 characters,  if >100, then continue to step 2
  2.  Use left function to get 100 characters of the string
  3.  Reverse the string returned from left
  4.  Get a substring of the reversed truncated string from the first space (‘ ‘) to the end of the reversed truncated string
  5. Reverse the final string (So it is back to the correct direction of text)
  6. Append the ellipsis to the string ( or add a link or leave off if no extra chars are required)

 

Tags: ,

Leave a Reply