Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to list comma separated field data...?

leonhardtkOct 29 2012 — edited Oct 30 2012
I need to split out list data in a column in order to made the data more readable.

For example:

select * from mytable;
ID     TEXT
--     -----------
1      RED,GREEN,BLUE,YELLOW,ORANGE,VIOLET
2      GREEN,ORCHID,SLATEBLUE,RED,YELLOW
What I would like to see instead:
ID     TEXT
--     -----------
1      RED
       GREEN
       BLUE
       YELLOW
       ORANGE
       VIOLET
2      GREEN
       ORCHID
       SLATE
       RED
       YELLOW
The number if items in the TEXT column ranges from a minimum of four items..it also may have have over 20!
The elements are all separated by a comma (no space).
Finally, the for now, the data does not need to be sorted, but would be nice in the future!

Thanks, I really appreciate any insight you all might be able to provide!!!

KSL.
This post has been answered by Stew Ashton on Oct 29 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 27 2012
Added on Oct 29 2012
24 comments
657 views