1

Referencing this SO question

Postgres unnest

Is there a way to split a column based on 2 delimiters into 4 columns?

If I have a column with this data in it.

11-3-1-4 $72390.00

How would I make it 

col1    col2    col3    col4    col5
11      3       1       4       72390.00

Plus should I retain the original column ?

Community
  • 1
  • 1
sayth
  • 6,696
  • 12
  • 58
  • 100

1 Answers1

1

string_to_array() can be used for this:

select c1[1] as col1, 
       c1[2] as col2, 
       c1[3] as col3,
       c1[4] as col4,
       substr(col5, 2) as col5
from (
   select string_to_array((string_to_array(the_column, ' '))[1], '-') as c1,
          (string_to_array(the_column, ' '))[2] as col5
   from the_table
) t