How to put strings separated by a ; in different columns-CodePudding

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  |

CodePudding user response：

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  |

CodePudding user response：

An alternate to regexp_split_to_array is split_part, which functions similar in this circumstance:

select name
    , split_part(lastname, ';', 1) last
    , split_part(lastname, ';', 2) name
from fullname
;