PostgreSQL: how can I shorten a multi-word string to few words?

Question

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

score 6 · Accepted Answer · answered May 27 '17 at 19:48

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.

score 2 · Answer 2 · answered May 27 '17 at 22:51

Use a regular expression, like so:

select substring('hello everyone out there somewhere', '[^ ]+[ ]+[^ ]+[ ]+[^ ]+');

Slightly bigger example, which includes examples of handling apostrophes in string literals (the sentences have been taken from another question):

create table sentences ( s varchar(128) ) ;

insert into sentences values 
('How to left join and count two tables where the rows (strings) are different'),
('I have two tables - t1, t2.'),
('t1 has a title column with titles separated by spaces.'),
('t2 has a path column with paths separated by dashes and prefixed ''path/'''),
('GOAL: FOR EACH t1.title COUNT THE NUMBER OF TIMES IT SHOWS UP IN t2'),
('Course of action: parse t2.path so that it looks like t1.title in order to do a left join and count'),
('I''m very new to this, if you have a better approach altogether I would appreciate any suggestions'),
('So far I have:'),
('select t1.title, count(t2.path) as num '),
('from t1 left join t2 on t1.title = substring(log.path from 6) '),
('where t1.title like ''%''||split_part(substring(log.path from 6),''-'',1)||''%'' 
'),
('group by articles.title; ');

-- pick the first 3 words
select substring(s, '[^ ]+[ ]+[^ ]+[ ]+[^ ]+')
from sentences;

            substring            
---------------------------------
 How to left
 I have two
 t1 has a
 t2 has a
 GOAL: FOR EACH
 Course of action:
 I'm very new
 So far I
 select t1.title, count(t2.path)
 from t1 left
 where t1.title like
 group by articles.title;
(12 rows)

score 1 · Answer 3 · answered May 27 '17 at 18:53

You need to,

Split the string into an array with string_to_array.
Generate an array of the indexes with generate_series.
Resolve the index and array into a value.
Aggregate the values into an array with array_agg
Render the array as a string, converting it back with array_to_string

Here is an example.

SELECT array_to_string(array_agg(arr[i]), ' ')
FROM string_to_array('hello everyone out there somewhere',' ') AS t(arr)
CROSS JOIN generate_series(1,3) AS gs(i);  -- 3 (first three)

Alternatively, you can

SELECT ... unnest(string_to_array) ... LIMIT to get an array equal to the first x elems.
Convert that with array_to_string.

Example (adapted from Magnus's answer here)

SELECT array_to_string(
  ARRAY(
    SELECT
    unnest(string_to_array('hello everyone out there somewhere',' ')) 
    LIMIT 3
  ),
' '
);

score 0 · Answer 4 · answered May 27 '17 at 20:53

Assuming that the only word separator is a space, I would use the following regexp: '(([^ ]+ ){0,2}[^ ]+)'

SELECT
     (regexp_matches('hello everyone out there somewhere', '(([^ ]+ ){0,2}[^ ]+)', ''))[1] AS a,
     (regexp_matches('hello everyone',                     '(([^ ]+ ){0,2}[^ ]+)', ''))[1] AS b,
     (regexp_matches('this is good',                       '(([^ ]+ ){0,2}[^ ]+)', ''))[1] AS c,
     (regexp_matches('this is all assuming the only word separator is a space', '(([^ ]+ ){0,2}[^ ]+)', ''))[1] AS d,
     (regexp_matches('shortest',                           '(([^ ]+ ){0,2}[^ ]+)', ''))[1] AS e ;

a                  | b              | c            | d           | e       
:----------------- | :------------- | :----------- | :---------- | :-------
hello everyone out | hello everyone | this is good | this is all | shortest

dbfiddle here

If you need to use other word separators, the regexp can be changed to accomodate for those needs.

score 0 · Answer 5 · answered May 28 '17 at 22:27

Technically this isn't what you asked for, but you could write a condition like this:

book.title ILIKE 'hello everyone out %'

ILIKE is PostgreSQL's non-case-sensitive LIKE. title has to match the pattern up to % then it doesn't matter what follows.

This version assumes that book.title starts with three words and three spaces. So "I, Claudius" or "Uncle Tom's Cabin" might need special care. Maybe that's why you haven't done it this way.

If a result that is not absolutely accurate is acceptable, then you can put less work into it. ;-)

PostgreSQL: how can I shorten a multi-word string to few words?

5 Answers5