title: oracle -- Split multiple comma separated values in oracle table to multiple rows
body: <p>I Have a problem with oracle split query.</p>
  
  <p>While splitting comma separated data into multiple rows using connect by and regular expression in oracle query I am getting more <strong>duplicate rows</strong>. 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.</p>
  
  <p>I tried the following query however it's generating duplicate rows in query result:</p>
  
  <pre><code>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 &lt;= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>The above select query is only able to split a <strong>single comma delimited string</strong>, however, it produces duplicate rows when executed on a table with <strong>multiple rows</strong>. How to restrict the duplicate rows?</p>