CODEWITHSUNDEEP
sqlregexpostgresql
Andrus
Andrus

Reputation: 27909

How to extract first and remaining words in postgres

Postgres database table contains words separated by spaces in char(20) column. Result from select statement should be table containing two columns.

First column should contain first word. Second column should contain remaining words. For example

create temp table test (name char(20)) on commit drop ;
insert into test values
('word11 word12 word13'),
('word21'),
('word31 word32');

SELECT 
        ? as firstword,
        ? as remainingwords
from test;

Should produce

firstword     remainingwords
word11        word12 word13
word21
word31        word32

What expressions can used in place of ? marks to produce this. Can some regexp used or other solution ?

Using PostgreSQL 9.1.2

Upvotes: 2

Views: 2915

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Convert the values to an array and use that:

select (string_to_array(t, ' '))[1] as first_word,
       (string_to_array(t, ' '))[2:99]
from test;

Arrays are easier to work with than strings, particularly if you have lists within a row. However, you can convert back to a string if you like using array_to_string().

In your version of Postgres, this is more accurately written as:

select (string_to_array(t, ' '))[1] as first_word,
       (string_to_array(t, ' '))[2:cardinality( string_to_array(t, ' ') )]
from test;

In Postgres 9.6, this shorthand is supported:

select (string_to_array(t, ' '))[1] as first_word,
       (string_to_array(t, ' '))[2:]
from test;

Upvotes: 4

Related Questions