1

I found this PHP code that returns a sub-string ending with a whole word (IE separated by a space):

if ( strlen( $body ) < $maxlength ) return $body;
$body = substr( $body , 0 , $maxlength );
$rpos = strrpos( $body , ' ' );
if ( $rpos > 0 ) $body = substr( $body , 0 , $rpos );
return $body;

Is it possible to achieve the same result using pgsql's string manipulation functions?

1

Something like this should work (here 15 is the equivalent of $maxlength):

SELECT
  CASE WHEN LENGTH(mycolumn) <= 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.