split a row in a table into multiple rows by parsing the string value in the column

Question

I need to split a row into multiple rows.

sample value in a column is like this.

Message for status: warnings: [w1,w2] | errors: [e1,e2] | other_errors: [e3]

Above total message is in one column value. I need to split it multiple rows like

w1
w2
e1
e2
e3

Can some one help me how to do this?

possible duplicate of [Splitting comma separated string in a PL/SQL stored proc](http://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc) — APC, Apr 30 '13 at 13:06
For the purpose of a select, or are you inserting these rows into another table? — David Aldridge, Apr 30 '13 at 13:08
Output if the select query should get me the result like this. Which may be stored in another table. — Mahesh, Apr 30 '13 at 14:21

score 0 · Answer 1 · answered Apr 30 '13 at 20:02

Here's one method for splitting into columns:

select
  ltrim(regexp_substr(translate(col1,'[]',',,'),',([^,]+)', 1,1) ,',') s1,
  ltrim(regexp_substr(translate(col1,'[]',',,'),',([^,]+)', 1,2) ,',') s2,
  ltrim(regexp_substr(translate(col1,'[]',',,'),',([^,]+)', 1,4) ,',') s3,
  ltrim(regexp_substr(translate(col1,'[]',',,'),',([^,]+)', 1,5) ,',') s4,
  ltrim(regexp_substr(translate(col1,'[]',',,'),',([^,]+)', 1,7) ,',') s5
from
  t1;

Then you need to unpivot it, and there are plenty of examples on SE.

split a row in a table into multiple rows by parsing the string value in the column

1 Answers1