Thursday, 12 September 2013

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

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

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
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

No comments:

Post a Comment