Postgres: limit length of returned string but needs to end on a full word

Question

With Postgres I am trying to truncate a string before returning it so that I can display it in a partial results list client-side. The key is that it needs to end on a full word. I've gotten as far as: SELECT comments, substr(comments, 1, 80) AS "trunc" FROM book; Would the next step be to do a substring of the substring with RegEx (perhaps trying to make sure the string ends on a space)?

Answer 1

Use substring(string from pattern). Example for 12 characters:

with the_data(comments) as (
    values
        ('follow me'::text),
        ('abcdef ghijkl'),
        ('abcd efgh ijkl'),
        ('abc def ghi jkl'),
        ('ab cd ef gh ij kl')
    )

select substring(left(comments || ' ', 12) from '.*\s')
from the_data;

  substring   
--------------
 follow me 
 abcdef 
 abcd efgh 
 abc def ghi 
 ab cd ef gh 
(5 rows)