Split a comma separated string of unknown elements to multiple columns in PostgreSQL 11.0

Question

I have a table in PostgreSQL with following values.

col1
; substrate
positive allosteric modulator
inducer; substrate

I would like to split the row values by ';' into multiple columns. As per my understanding, split_part() function works only for fix number of values.

How can I get the below output?

col1                                col2                            col3
; substrate                                                         substrate                            
positive allosteric modulator       positive allosteric modulator
inducer; substrate                  inducer                         substrate

Thanks

user330315 · Accepted Answer · 2022-01-19 21:59:37Z

1

You can split it into an array, then access each array element:

select col1,
       elements[1] as col2, 
       elements[2] as col3
from (
  select col1, regexp_split_to_array(col1, '\s*;\s*') as elements
  from the_table
) t

answered Jan 19, 2022 at 21:59

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

rshar Over a year ago

What if I do not know the maximum number of array elements?

user330315 Over a year ago

@rshar: you have to use some value. One fundamental restriction of SQL is, that the number, names and data type of all columns of a query must be known before the query is started. You can't have a dynamic number of columns.

Collectives™ on Stack Overflow

Split a comma separated string of unknown elements to multiple columns in PostgreSQL 11.0

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related