Extract string items into rows?

Moderator: NorbertKrupa

Post Reply
debfawn
Beginner
Beginner
Posts: 47
Joined: Tue Jan 29, 2013 2:30 pm

Extract string items into rows?

Post by debfawn » Tue Mar 07, 2017 7:42 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Extract string items into rows?

Post by JimKnicely » Tue Mar 07, 2017 7:49 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

debfawn
Beginner
Beginner
Posts: 47
Joined: Tue Jan 29, 2013 2:30 pm

Re: Extract string items into rows?

Post by debfawn » Tue Mar 07, 2017 8:32 pm

Wow, thanks for the quick response! How the heck did you think to do it that way :D I am impressed!

Post Reply

Return to “New to Vertica SQL”