oracle -- Split multiple comma separated values in oracle table to multiple rows

Question

I Have a problem with oracle split query.

While splitting comma separated data into multiple rows using connect by and regular expression in oracle query I am getting more duplicate rows. for example actually my table having 150 rows in that one two rows having comma separated strings so overall i have to get only 155 rows but i am getting 2000 rows. If i use distinct its working fine but i dont want duplicate rows in query result.

I tried the following query however it's generating duplicate rows in query result:

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
              UNION 
              SELECT 'f,g',2 from dual
              UNION 
               SELECT 'h',3 FROM DUAL)

SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) ,SLNO FROM CTE 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1

EDIT

The above select query is only able to split a single comma delimited string, however, it produces duplicate rows when executed on a table with multiple rows. How to restrict the duplicate rows?

Answer 1

Finally I came up with this answer

WITH CTE AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
              UNION
              SELECT 'f,g' temp, 2 slno FROM DUAL
              UNION
              SELECT 'h' temp, 3 slno FROM DUAL)
SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
FROM CTE
CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
    AND PRIOR slno = slno
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

Answer 2

Try like this,

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
              UNION 
              SELECT 'f,g',2 from dual
              UNION 
              SELECT 'h',3 FROM DUAL)
     SELECT regexp_substr (temp, '[^,]+', 1, rn)temp, slno
     FROM   cte
     CROSS JOIN 
     (
          SELECT ROWNUM rn
          FROM  (SELECT MAX (LENGTH (regexp_replace (temp, '[^,]+'))) + 1 max_l
                 from cte
                 )
          connect by level <= max_l
         )
     WHERE regexp_substr (temp, '[^,]+', 1, rn) IS NOT NULL
     order by temp;

Answer 3

without using connect by:

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
      UNION 
      SELECT 'f,g',2 from dual
      UNION 
       SELECT 'h',3 FROM DUAL
)
,x as (
  select
  ','||temp||',' temp
  ,slno
  from CTE
)
,iter as (SELECT rownum AS pos
    FROM all_objects
)
select
SUBSTR(x.temp
  ,INSTR(x.temp, ',', 1, iter.pos) + 1
  ,INSTR(x.temp, ',', 1, iter.pos + 1)-INSTR(x.temp, ',', 1, iter.pos)-1
) temp
,x.slno
from x, iter
where iter.pos < = (LENGTH(x.temp) - LENGTH(REPLACE(x.temp, ','))) - 1;