pgsql Return Partial Text Field Ending In A Whole Word I found this PHP code that returns a sub-string ending with a whole word (IE separated by a space):

if ( strlen( $body ) &lt; $maxlength ) return $body;
$body = substr( $body , 0 , $maxlength );
$rpos = strrpos( $body , ' ' );
if ( $rpos &gt; 0 ) $body = substr( $body , 0 , $rpos );
return $body;


Is it possible to achieve the same result using pgsql's string manipulation functions?
Something like this should work (here 15 is the equivalent of $maxlength):

SELECT
  CASE WHEN LENGTH(mycolumn) &lt;= 15
       THEN mycolumn
  ELSE SUBSTRING(mycolumn, 0, 15 + 1 -
                 POSITION(' ' IN REVERSE(SUBSTRING(mycolumn, 0, 15 + 1))))
  END
  FROM mytable


Here is a working demo.