Splitting Comma Seperated String to RowsPosted: April 15, 2008
Got the following email from one of my users:
I am trying to write a SQL select to get comma delimited values from one field in rows. For example I have a Table T1 create table t1(a varchar2(30), b varchar2(30)); insert into t1 values ('A','27.68%,2.78%,69.55%'); SELECT A, B FROM T1; A B ===== ===== A 27.68%,2.78%,69.55% Can you help me write a SQL which will give the following output? A PERCENT ====== ======== A 27.68% A 2.78% A 69.55%
My solution is rather ugly, but it works (on 10g and above) and makes no assumptions about the number of elements in the list :
With t2 as (select A,','||B||',' as B from t1) SELECT A,SUBSTR(B,INSTR(B,',',1,LEVEL)+1,INSTR(B,',',1,LEVEL+1)-INSTR(B,',',1,LEVEL)-1) FROM t2 CONNECT BY LEVEL <= length(regexp_replace('[^\,]',''))-2 ;
Senior DBA had a simpler solution: “Just replace all the commas with new lines”.