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!

convert comma separated string into multiple rows+connect by loop error

Sreekanth MunagalaAug 7 2009 — edited Mar 10 2011
Hi friends,
I have a column which contains values like
FA
FA,DA
FA,DA,CA

Required o/p
FA
FA
DA
FA
DA
CA

I tried this way.But it is erroring out.
SELECT or_str,
        CASE WHEN LEVEL = 1 
             THEN SUBSTR(or_str,LEVEL,INSTR(or_str,',',1,LEVEL)-1)
			 WHEN INSTR(or_str,',',1,LEVEL) = 0 
			 THEN SUBSTR(or_str,INSTR(or_str,',',1,LEVEL-1)+1,LENGTH(or_str)-INSTR(or_str,',',1,LEVEL-1))
			 ELSE SUBSTR(or_str,INSTR(or_str,',',1,LEVEL-1)+1,(INSTR(or_str,',',1,LEVEL)- (INSTR(or_str,',',1,LEVEL-1)+1)))
		END SubString,
		INSTR(or_str,',',1,LEVEL),
		LEVEL			 
FROM 
(
 SELECT 'as,df' or_str FROM dual UNION ALL
 SELECT 'asass,df,sdas' FROM dual 
)
CONNECT BY LEVEL <= LENGTH(or_str)-LENGTH(TRANSLATE(or_str,'A,','A'))+1
AND PRIOR or_str = or_str
can you please tell me where am i going wrong.

My database version is 9i;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 7 2011
Added on Aug 7 2009
6 comments
2,403 views