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
Extract string items into rows?
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Extract string items into rows?
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Extract string items into rows?
Wow, thanks for the quick response! How the heck did you think to do it that way I am impressed!