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;