table1
ID ITEM ----------------------- 1 a|b|c 2 x|y|z 3 Q||S 4 |||| 5 J|K|Y|U
I want to insert above ITEM to table2 as following manner
table2
SLNO ID ITEMS ------------------------ 1 1 a 2 1 b 3 1 c 4 2 x 5 2 y 6 2 z 7 3 Q 8 3 S 9 5 J 10 5 K 11 5 Y 12 5 U
so i have used
INSERT INTO table2("ID","ITEMS")SELECT ID,split_part("ITEM", '|',1) AS ITEMS FROM table1
the problem is split_part() need to specify a postion index,so it only split and shows first positions charaters means,
ID ITEMS ----------- 1 a 2 x 3 Q 5 J
So how i acheive above
Advertisement
Answer
You can convert the string to an array, then unnest that array:
select t.id, i.item from the_table t cross join unnest(string_to_array(t.items, '|')) as i(item);
If you want to exclude the empty elements (e.g. in the line 4 ||||
you can use:
select t.id, i.item from the_table t cross join unnest(string_to_array(t.items, '|')) as i(item) where nullif(trim(i.item),'') is not null;