Page 1 of 1

Extract string items into rows?

Posted: Tue Mar 07, 2017 7:42 pm
by debfawn
Hi all,

Anyone know how I can convert a comma separated string of items into rows? So if I have a string like A,B,C,D I want to have 4 rows, one with A, one with B, one with C and finally, one with D. Is that possible?

Thank you

Re: Extract string items into rows?

Posted: Tue Mar 07, 2017 7:49 pm
by JimKnicely
Here is one way:

dbadmin=> select split_part('A,B,C,D', ',', rn) split_into_rows from dual
dbadmin-> cross join (select row_number() over () rn from columns) foo
dbadmin-> where rn <= regexp_count('A,B,C,D', ',')+1;
split_into_rows
-----------------
A
B
C
D
(4 rows)

Re: Extract string items into rows?

Posted: Tue Mar 07, 2017 8:32 pm
by debfawn
Wow, thanks for the quick response! How the heck did you think to do it that way :D I am impressed!