PostgreSQL – How to Shorten a Multi-Word String to Few Words?

arraypostgresqlstring

I'm using PostgreSQL 9.5.6. I have space separated multi-word strings that I need to shorten to say 3 words?

I've looked at the documentation and in order to use the substring() functions I need to know the index position of the char where I want to start extraction, but the strings I'm working with are varying length.

I've also looked at split_part() and that only returns one word.

How can I get something like: 

hello everyone out there somewhere

shortened to

hello everyone out

Best Answer

Turn the sentence into an array, then pick the first three elements and concatenate them back to a single string: 

select array_to_string ((regexp_split_to_array(title, '\s+'))[1:3], ' ')
from book;

alternatively, just use split_part() three times: 

select split_part(title, ' ', 1)||
       split_part(title, ' ', 2)||  
       split_part(title, ' ', 3)
from book;

split_part() has the disadvantage, that you can't properly deal with multiple spaces between the words.

Related Question