How to split a string into rows by separating with any value (such as comma) in oracle sql ?

I am sure, you will find ton of articles, blogs or codes on this topics. but this one is unique in the sence of performance with lot of data.  Normally use of CONNECT BY keyword is very costly but try this one with large set of data.

Note: you need to minimize your data set as needed by using the sub-query as like I did otherwize CONNECT BY will hurt your performance a lot when you will applied into a full table.

(Change the red marked places with any other values as per your requirement)


SELECT *
FROM
  (SELECT a.before_split ,
    trim(regexp_substr(a.before_split, '[^,]+', 1, LEVEL)) after_split
  FROM
    (SELECT 'a,b,c,de,ff,gh,i,kl,l,m' before_split
    FROM dual ---or you can use a big table here a as source
    -- where ...
    ) a
    CONNECT BY instr(before_split, ',', 1, LEVEL-1) > 0
  ) ;



Output- as below- 

 

Cheers..Halim