How to put strings separated by a ; in different columns

Question

So I am trying to put two strings in different columns but it doesn't work with Substring because I use string_agg

So I am trying with SUBSTRING but I don't know how SELECT name, SUBSTRING(string_agg(lastname, ','),0) as last, SUBSTRING(string_agg(lastname,','),1) as name as FROM fullname

Expected this to happen

name | last | name       
--------+--------+
 jef  | yes   |  no
 oki  | why  | what

but nothing changed so it stayed like this

name | lastname |         
--------+--------+
 jef  | yes, no   |  
 oki  | why, what  |

Answer 1

use regexp_split_array function to split column value.

select name,
(regexp_split_to_array(last_name,';'))[1] as last,
(regexp_split_to_array(last_name,';'))[2] as name
from table100;

output:

name|last|name|
----+----+----+
jef |yes |no  |