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

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

Solution 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