【Question】

I have a table something like this:

COL1    COL2 

a,b,c,d    h  

 

The desired result:

COL1    COL2 

a    h 

b    h 

c    h 

d    h 

Is there any very simple solution? Thanks.

 

【Answer】

You can use hierarchical queries cooperated with subqueries or random numbers to get it done in Oracle SQL. Here are two solutions:

Solution 1:

SELECT col1,REGEXP_SUBSTR(col1,'\[^,\]+',1,rn) col12,col2 

FROM t0052,

  (SELECT LEVEL rn FROM DUAL 

   CONNECT BY LEVEL<=(SELECT

   MAX(length(trim(translate(col1,replace(col1,','),' '))))+1

   FROM t0052)) 

WHERE REGEXP_SUBSTR(col1,'\[^,\]+',1,rn) IS NOT NULL

Solution 2:

select regexp_substr(col1,'\[^,\]+',1,level) col1,col2 

from t0052 

connect by level <= (length(col1)-length(regexp_replace(col1,'\[^,\]+',''))) 

and rowid= prior rowid 

and prior dbms_random.value is not null;

 

Both solutions use Oracle hacks (such as “prior dbms_random.value is not null” for avoiding loop errors). They are not very simple for common users.

You can also do it in SPL (Structured Process Language):

	A
1	$SELECT COL1,COL2  FROM t0052
2	=A1.news(COL1.array();~:COL1,COL2)

The SPL script is simple and easy to understand. Here’s A2’s result:

esProc offers JDBC interface to be easily embedded into a Java application. See How to Call an SPL Script in Java.